Slow SQL Calls

Question

Slow SQL Calls

kelly-895141

Grasshopper

Points: 22
More actions
October 1, 2008 at 2:21 pm

#103591

We are calling sql queries to our database using multiple counts, a sum and a select statement. Does any one have ideas on how to speed this up?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Appropriate indexes?

Not really enough info. Can you post the table structure, the existing indexes and the query. Also an idea of the number of rows in the table would help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 2

And, as always, an execution plan or two.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

something like this?

SELECTSUM(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) AS countReally,

SUM(Points) AS OrdinarySum,

SUM(CASE WHEN Status = 'B' THEN Points ELSE 0 END) AS casedSum

FROMTable

N 56°04'39.16"
E 12°55'05.25"

kelly-895141 Grasshopper Points: 22 More actions · Answer 4

SELECT COUNT(ID) AS intX, Keyword,

COUNT(CASE WHEN IsOrder=1 THEN ID END) AS intY,

SUM(Amount) AS Total FROM tblTrack

WHERE Sites_ID=" & session("SiteID") & "

AND DateTime >= '" & StartDate & "'

AND DateTime <= '" & EndDate") & "'

AND Paid=" & Paid & "

GROUP BY " & GroupBy & "

ORDER BY intX " & OrderType

There is just the basic indexes in there, and it's about only 200,000 records

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 5

You might want to lookup the term "sql injection" because you're pretty vulnerable.

First, try using BETWEEN instead of >= <= on the date fields.

Can you post the execution plan?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

kelly (10/2/2008)
There is just the basic indexes in there, and it's about only 200,000 records

Basic indexes meaning what exactly? Please post the table structure and the index definitions. Remeber that we can't see the system and haven't worked on the issue, so what seems obvious to you isn't to us.

The query's fairly simple, so it's most likely that the indexes are not appropriate for the query.

What are typical values of 'GroupBy' ?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Grant Fritchey (10/2/2008)
First, try using BETWEEN instead of >= <= on the date fields.

Ummm... why, Grant? They BOTH appear in the >= <= form after the optimizer evaluates them. Is there some advantage to BETWEEN that I'm not aware of?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 8

I thought BETWEEN was sargeable and >= <= was not. I just did some tests. I'm wrong... again... I'm going to stop posting now. That's way too many errors.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

Might not be a mistake on your part... seems to me that, way back when, what you say used to be the truth and they fixed it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)