September 23, 2005 at 2:56 pm
Hello,
I have the following query, which looks pretty simple to me, but takes up to 0:00:48 seconds to bring the results of 409147 rows out of FROM listed table with 1150222 rows in it and INNER JOIN listed table with 151917 rows in it. Both tables in the query have proper primary key and indexes on the WHERE clause field and JOIN field. I don't understand why it must take nearly a minute to run and return the result set!? Below the query is the execution plan.
*****************************
DECLARE @FuSSIN VARCHAR(9)
SET @FuSSIN = '123456789'
SELECT
C.Account as [Account Nbr],
Year(getdate()) - Year(C.BirthDt) as [Age],
isnull(C.ObjectiveName,'') as [Objective],
C.AnnualIncomeRange as [Annual Income],
C.NetWorthRange as [Net Worth],
C.RepID as [Rep ID]
FROM t_MemberAccount C
INNER JOIN t_HAFAccount A
ON C.SSIN = A.FuSSIN
WHERE C.SSIN=@FuSSIN
GROUP BY C.Account, BirthDt, ObjectiveName, AnnualIncomeRange,NetWorthRange,C.Repid
*************************************
Bookmark Lookup on t_MemberAccount with PREFETCH cost is 38%
Non-Clustered Index Scan on t_MemberAccount.SSIN, Seek: SSIN=@FuSSIN ORDERED FORWARD cost is 62%
Total: 100%
*************************************
If I remove the INNER JOIN and run the query it still takes the same amount of time to execute returning the same # of rows in the result set. I have run similar queried before against similar data size tables, but they didn't take this long. The query is simple and query plan looks simple, too so I do not understand why it takes up to .48 seconds.
Please suggest if you can of any improvements or different way of writing the query so that it's faster in execution time.
Thanks much.
JN
September 23, 2005 at 3:01 pm
Can you make t_MemberAccount.SSIN CLUSTERED ?
* Noel
September 23, 2005 at 3:03 pm
No, because both the tables already have a primary key defined as Clustered Index, which are different than t_MemberAccount.SSIN field.
September 23, 2005 at 3:07 pm
That still does not means that you can't change the primary key to non-clustered and this one to clustered
* Noel
September 23, 2005 at 3:11 pm
Well, yeah, but I am doing performance tuning on another DBA's DB so wasn't sure if I should take the liberty to change her DB design! But, perhaps I can test with your suggestion and if it improves then suggest the DBA to keep the changes permanently! This is still on DEV anyway.
Let me give it a shot. If you see anything else, let me know.
September 23, 2005 at 3:19 pm
Ok, it did improve, but still NOT within expected range, I think. It took .33 seconds to run and query cost now was 100% Clustered index seek. Oh, this is even without the JOIN part.
September 23, 2005 at 3:32 pm
If you changed to clustered the Bookmark lookup should be gone!
the fastest operation you can get is a clustered index seek. And you are there! Now you may be running into lock escalation issues because you are returning a lot of rows you may try to use the with (nolock) hit if apropriate, make sure the resources (CPU and MEMORY) on your server are adecuate, that no process is contending with sql, that disk access is fast (RAID LEVEL , separate logs and data,etc) and that tempdb has necessary space not to grow in the middle of a request, That your indexes are in good shape (Fragmentation is low and statistics are uptodate)
ALL those factors come into play and you need to determine which maybe affecting you.
Cheers!
Have a good weekend...
* Noel
September 23, 2005 at 3:40 pm
You don't actually seem to be using t_HAFAccount so why use it in the join. Assuming that you are actually going to use it and the query isn't finished yet then my comments would be:
Qualify all objecs with the owner ie. dbo.t_HAFAccount
Is t_MemberAccount.Account the Clustered Primary Key?
Try putting the following in your WHERE clause
AND C.Account BETWEEN 0 AND 2147483647
It looks superfluous but I have seen it trick the Query Optimiser into using the Clustered index.
DECLARE @lYear SMALLINT
SET @lYear=YEAR(GETDATE())
Year(GetDATE()) is a constant so change the line
Year(getdate()) - Year(C.BirthDt) as [Age],
TO
@lYear - - Year(C.BirthDt) as [Age],
Better still, do the age thing in the client app.
Try WHERE A.FuSSIN = @FuSSIN instead.
Do you really need all the items in the GROUP BY?
You could place a covering index on those items, that is an single index with the items in it.
As your search is on your linking fields then why not preselect the records in derived queries.
DECLARE @FuSSIN VARCHAR(9) ,
@lYear SMALLINT
SET @FuSSIN = '123456789'
SET @lYear=YEAR(GETDATE())
SELECT DT.[Account Nbr] ,
DT.Age ,
DT.Objective ,
DT.[Annual Income] ,
DT.[Net Worth] ,
DT.RepId
FROM (
SELECT
C.SSIN,
C.Account as [Account Nbr],
@lYear - Year(C.BirthDt) as [Age],
isnull(C.ObjectiveName,'') as [Objective],
C.AnnualIncomeRange as [Annual Income],
C.NetWorthRange as [Net Worth],
C.RepID as [Rep ID]
FROM dbo.t_MemberAccount C
WHERE C.SSIN=@FuSSIN
) AS DT1
INNER JOIN (
SELECT A.FuSSIN
FROM dbo.t_HAFAccount A
WHERE A.FuSSIN = @FuSSIN
) AS DT2
ON DT1.SSIN = DT2.FuSSIN
GROUP BY DT.Account, DT.BirthDt, DT.Objective, DT.AnnualIncomeRange,DT.NetWorthRange,DT.Repid
September 23, 2005 at 4:10 pm
Tried your suggested query design and still no difference - takes .29 to .33 seconds to execute.
The server has 1 processor and 2047 OP Memory. Log and data files are separated on to seperate disks of their own. TempDB is of 251.50 MB size and has 238.86 of free available space to grow. Everything looks as good as it gets, I think so can't figure out why such a slow running query!
Now, the table has Clustered index on the SSIN field so Account Nbr is the primary key, but not clustered.
Any other suggestions?
JN
September 23, 2005 at 9:05 pm
What's the target response time you're looking for? Is there anyhing else running on the box? How much memory is SQL Server actually using?
September 24, 2005 at 5:49 am
Don't know if pushing the where clause in the join syntax yields more performance.
DECLARE @FuSSIN VARCHAR(9)
SET @FuSSIN = '123456789'
DECLARE @lYear SMALLINT
SET @lYear=YEAR(GETDATE())
SELECT
C.Account as [Account Nbr],
@lYear- Year(C.BirthDt) as [Age],
isnull(C.ObjectiveName,'') as [Objective],
C.AnnualIncomeRange as [Annual Income],
C.NetWorthRange as [Net Worth],
C.RepID as [Rep ID]
FROM dbo.t_MemberAccount C
INNER JOIN dbo.t_HAFAccount A
ON C.SSIN = A.FuSSIN
AND t_MemberAccount C=@FuSSIN /*WHERE CLAUSE*/
GROUP BY C.Account, BirthDt, ObjectiveName, AnnualIncomeRange,NetWorthRange,C.Repid
September 24, 2005 at 1:22 pm
I was hoping for .10 seconds or even less. I have to check how much memory SQL Server is utilizing when I am back at work on Monday. There is nothing else going on in the server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply