October 13, 2009 at 12:59 pm
Greetings,
I have gone around and around this particular problem and cannot find a fix for it. I was hoping I could receive some help from smarter people...
I am running sql 2005 as the backend and an accouting app, in citrix, as the frontend.
There is a stored procedure that I believe could run much faster...
Select * from TABLE1
where company=@Comp
and (@Prop is null or property=@Prop or property is null)
order by fieldA
This proc is being ran repeatly for a specific module to open in the frondend application. Every time it runs, it takes between 200 ms to 11 or more ms.
If I hard code it and specify fixed values of @comp and @Prop, the module takes 2 seconds to open.
The table has 300,000 records.
My dilema is that I cannot believe that SQL optimizer would take so long to return a set of rows when I have a clustered index on @comp & @Prop. I have tried different combinations, running with the recompile option, but have not been able to improve performance at all.
The module takes 10 minutes to open it.
Any ideas how to make this faster? shouldn't SQL be able to give me the same speed as it does when looking for a fixed value. I understand about the proc being cached in memory and so improving performance...but shouldn't sql give me a similar speed.
thanks,
DBA
PP
October 13, 2009 at 1:09 pm
How many rows is returned, of the 300 000 rows, and, why do you call it so much, is there a possibility to return all rows at once, so the front end will be able to handle all at the same time?
Also, can you provide the DDL for the table, the index definitions, as well as the attached .sqlplan, so I can look at it, we might be able to optimize it.
Hope that helps,
Cheers,
J-F
October 13, 2009 at 1:20 pm
Jee I think I've seen something like this before.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
You migth want to look at the query plans its building... and look at what Gail did here to make an neato parameterized dynamic sql thing.
October 13, 2009 at 1:29 pm
J-F, thak you.
That's a good question...It's returning 2,625 rows - so a minimal set from the whole total. It's called probably like 50 times or so. The application was designed that way. I would have rather called it once and do the whole thing in one shot - but the programmers didn't ask me 🙂 and it will take too much trouble for them to change it.
October 13, 2009 at 1:33 pm
What does the execution plan look like? Table Scans or Index Scans or Index Seeks.
Can you provide sample DDL and INSERT statements to stick some sample data into a table for us to play with?
I'd like to suspect that the (field = @field or @filed IS NULL Or field IS NULL) might be what is killing you but I'd need more to go on.
October 13, 2009 at 1:34 pm
What's the "DDL" for the table?
Index definition follows:
CX_tSCLedgerAccount_fAccount clustered located on PRIMARYfCompanyID
IX_tSCLedgerAccount_fAccountfCompanyIDfCapital_Ver_1.28.0022nonclustered located on PRIMARYfAccount, fCompanyID, fCapital
IX_tSCLedgerAccount_fCapital__Ver1.28.0022nonclustered located on PRIMARYfCapital
IX_tSCLedgerAccount_fCompanyID__Ver1.28.0022nonclustered located on PRIMARYfCompanyID
IX_tSCLedgerAccount_fCompanyID_Incl_all_Ver1.28.0022nonclustered located on PRIMARYfCompanyID
IX_tSCLedgerAccount_fCompanyIDfPropertyIDfAccount__Ver1.28.0022nonclustered located on PRIMARYfCompanyID, fPropertyID, fAccount
IX_tSCLedgerAccount_fProjectID__Ver1.28.0022nonclustered located on PRIMARYfProjectID
IX_tSCLedgerAccount_fPropertyID__Ver1.28.0022nonclustered located on PRIMARYfPropertyID
IX_tSCLedgerAccount_fType__Ver1.28.0022nonclustered located on PRIMARYfType
PK_tSCLedgerAccountnonclustered, unique, primary key located on PRIMARYfCompanyID, fAccount
-----
I do not have a sqlplan. I created the following one just today but with no improvements.
sp_create_plan_guide N'PlanGuide1',
N'SELECT * FROM Table1 WHERE fCompanyID = @CompID
AND (@PropID IS NULL OR fPropertyID IS NULL OR @PropID = fPropertyID)
ORDER BY fAccount',
N'SQL',
null,
N' @PropID uniqueidentifier, @CompID uniqueidentifier',
N'Option (Optimize for (@CompID=N''A1A3B6F0-09FB-4DD3-9007-E5F9999E3C57'', @PropID=N''54B7C9EC-4507-454A-B19C-F55E94B19F86'' ))'
-------
October 13, 2009 at 1:42 pm
You are in sql2005? Click include actual plan, in the toolbar, and right click the execution plan, save as, and add to a zip, to post here, we will be able to help with that.
Also, the DDL would be the create table with the create indexes statements, so we can reproduce a part of your data easily.
Don't you think this is a bit of overhead, returning 2000 rows to the application, 50 times? even if we improve the query, to let's say 20 ms, it will still take you 1 second to complete all of that, without calculating the time it takes to process whatever else you are doing.
I say this is an application problem, I strongly propose you review this.
Cheers,
J-F
October 13, 2009 at 2:02 pm
here is the execution plan...
If we get this proc to run 20 or 10 ms, it will be a success. The whole process consists mainly of these 50 runs of this proc.
thanks,
October 13, 2009 at 2:21 pm
Ok, first, you have several indexes that use the column fCompanyID as the first indexed column, and that includes different columns along with them. That is useless if your clustered key is already on fCompanyID.
Secondly, IMHO I don't think you can sort efficiently on a column unless it's the clustered key, I don't know if adding a NC index on the fAccount column would help for that. Some people might join in saying that I'm not right, and I would love to hear their explanations on what you can do to sort it.
What you could do is create a covering index for all the fields you need within this column, this is probably the faster you can get, but then, you still have the sorting to sort out, and that takes a considerable amount of time to sort, (48%) on the plan you've showed us.
Try to add an index on the fAccount column, and, maybe removing the useless indexes (fCompanyID), since the Cluster index is already on that column, see where that takes us, and provide a new plan from there.
Cheers,
J-F
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply