October 1, 2008 at 2:21 pm
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?
October 1, 2008 at 2:32 pm
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
October 2, 2008 at 5:33 am
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
October 2, 2008 at 7:16 am
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"
October 2, 2008 at 8:19 am
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
October 2, 2008 at 8:27 am
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
October 2, 2008 at 8:52 am
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
October 4, 2008 at 5:51 pm
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
Change is inevitable... Change for the better is not.
October 6, 2008 at 5:55 am
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
October 6, 2008 at 9:22 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply