Query with using an Interface between CRM system & SQL Server 2000

We have been experience performance problems on the CRM
system we use which is built on SQL Server 2000. I have
recently started the MCDBA course but do not have enough
experience to answer my own question...
What I have discovered is that all the maintenance of the
tables and relationship have been done through an
interface the CRM System uses which was meant to prevent
my colleagues from having to learn about SQL Server. As a
result when I try to produce diagrams in Enterprise
Manager no links are shown. Do you think this could be an
attribute to the system running slow as I can not see any
relationships between the tables on SQL Server. I think
another reason might be the lack of indexing on some of
the larger tables but any advice you can give would be
great!
Thank you for your time
AnnabelleJust becasue its a CRM database does not mean its been
optinised.
From what you have said there does not seem to be any
foreign keys defined on your system, however you can check
that yourself by
1. Go to EA
2. Select a table that you know should have foriegn key
links
3. Right Click and go to Design Table
4. Press the button on the top of your screen that
says 'Manage Relationships'
Then have a look if any foreign keys have been defined.
A first port of call on speeding up your selected would be
to create a profile then run it through the index tuning
wizard, this will give all the indexs that are needed to
speed things up.
After that you can post specific problems.
Peter
"Happiness is having a large, loving, caring, close-knit
family in another city."
George Burns
>--Original Message--
>We have been experience performance problems on the CRM
>system we use which is built on SQL Server 2000. I have
>recently started the MCDBA course but do not have enough
>experience to answer my own question...
>What I have discovered is that all the maintenance of the
>tables and relationship have been done through an
>interface the CRM System uses which was meant to prevent
>my colleagues from having to learn about SQL Server. As
a
>result when I try to produce diagrams in Enterprise
>Manager no links are shown. Do you think this could be
an
>attribute to the system running slow as I can not see any
>relationships between the tables on SQL Server. I think
>another reason might be the lack of indexing on some of
>the larger tables but any advice you can give would be
>great!
>Thank you for your time
>Annabelle
>.
>

Query using mathematical function of values from 2 tables has a performance prob

When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.

Why is this happening?
Is there a way to bypass this problem?

Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Long query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )

Short query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )Just a quick comment:
I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
The execution plan is assuming INNER JOINS which might not be what you want either.

Query too slow! Need some performance enhancing tips!

I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle_id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_item_assn] on
vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field_id=@.template_field_id) AND
([template_field].template_field_type_id=3) AND
([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
(vehicle.end_dtm IS NOT NULL)

Thanks
GavinOn 1 Dec 2004 06:17:40 -0800, gizmo@.consultant.com wrote:

>I have a stored procedure that queries a database using a Select
>statement with some inner joins and conditions. With over 9 million
>records it takes 1 min 36 sec to complete. This is too slow for my
>requirements.
>Is there any way I can optimize this query. I have thought about
>using an indexed view. I haven't done one before, does anyone know if
>this would have potential to improve performance or indeed any other
>performance enhancing techniques I might try.
>SELECT vehicle.vehicle_id
>FROM (( [vehicle]
>INNER JOIN [vehicle_subj_item_assn] on
>vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
>INNER JOIN [subj_item] on
>[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
>INNER JOIN [template_field] on
>[subj_item].subj_item_id=[template_field].subj_attr_id
>WHERE
>([template_field].template_field_id=@.template_field_id) AND
>([template_field].template_field_type_id=3) AND
>([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
>(vehicle.end_dtm IS NOT NULL)
>Thanks
>Gavin

Hi Gavin,

I don't think you need all those paretheses and brackets. They don't hurt
performance, but they do make the query harder to read.

Also, you never use any column in subj_item. I think you can remove that
table (unless it is ppossible that some value of subj_item_id that does
not exist in subj_item does exist in vehicle_subj_item_assn.subj_item_id
and template_field.subj_attr_id). I don't see any way to improve on the
remaining query:

SELECT vehicle.vehicle_id
FROM vehicle
INNER JOIN vehicle_subj_item_assn
ON vehicle.vehicle_id = vehicle_subj_item_assn.vehicle_id
INNER JOIN template_field
ON vehicle_subj_item_assn.subj_item_id=template_field .subj_attr_id
WHERE template_field.template_field_id = @.template_field_id
AND template_field.template_field_type_id = 3
AND vehicle_subj_item_assn.subj_item_value_text = @.value
AND vehicle.end_dtm IS NOT NULL
(untested)

An other way to optimize this is to look at your indexes. I don't think an
indexed view will do you much good (though you can always try, of course -
remember to test a typical workload, as this specific information will
return faster, but update performance will suffer) - indexed views are
often used for views with aggregations.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(gizmo@.consultant.com) writes:
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know if
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.

You could materialize the query into an indexed view, but I would suspect
that it is an overkill.

You can probably improve performance considerably by reviewing indexes.
96 seconds for 9 millions rows sounds like a table is being scanned
somewhere. However, I cannot do that for you, since you have not submitted
enough with information.

I would suggest that you post the CREATE TABLE and CREATE INDEX statments
for your tables, as well as the approxamite rowcount for the tables.

Also consider Hugo's note about the subj_item table. It may not fill a
function in the query. (Then again, it could serve as an EXISTS condition.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you checked out http://www.xprime.com

We've been using their database accelerator for MS SQL with good
results... 5X on many of our stored procs, similar to those you
describe below.

Tony

gizmo@.consultant.com wrote:
> I have a stored procedure that queries a database using a Select
> statement with some inner joins and conditions. With over 9 million
> records it takes 1 min 36 sec to complete. This is too slow for my
> requirements.
> Is there any way I can optimize this query. I have thought about
> using an indexed view. I haven't done one before, does anyone know
if
> this would have potential to improve performance or indeed any other
> performance enhancing techniques I might try.
> SELECT vehicle.vehicle_id
> FROM (( [vehicle]
> INNER JOIN [vehicle_subj_item_assn] on
> vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
> INNER JOIN [subj_item] on
> [vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
> INNER JOIN [template_field] on
> [subj_item].subj_item_id=[template_field].subj_attr_id
> WHERE
> ([template_field].template_field_id=@.template_field_id) AND
> ([template_field].template_field_type_id=3) AND
> ([vehicle_subj_item_assn].subj_item_value_text=@.value) AND
> (vehicle.end_dtm IS NOT NULL)
> Thanks
> Gavin

Query Timeouts after adding a logging table and SP

Hi everyone,
I hope you can help with some good suggestions. I have a system that was
working fine as far as performance, but after I added the following table and
SP, with a call to this SP from my Insert/Update/Delete queries, I started
getting timeout errors and the system came to a crawl. The first time it
happened I could not get it working until we rebooted the server. After that
it happened again the following day and then I backed out the calls to the
AddTableLog proc. Here is the table def and proc:
TableLogs definition
3 MessageId int 4 0
0 Message varchar 500 1
0 MessageDate datetime 8 1
0 AppLoggedInUser varchar 100 1
0 ComputerName varchar 100 1
0 CompLoggedInUser varchar 100 1
Proc to add to above table
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
Here is an example of how I was using the above log tables:
CREATE PROCEDURE [dbo].[nf_AddAttendance]
@.PatientId varchar(20),
@.AttendDate datetime,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, GetDate(), Session_User,@.ComputerName,
@.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Is there a better way to do this. I wanted to be able to log who actually
made the change and what computer they where at. So I pass that info in.
Would using a Trigger be faster at doing this? Or am I on the right track.
Thanks for any suggestions.
Michael LeeI forgot to mention that we are using SQL Server 2000.
Thanks again.
Michael Lee

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>