June 25, 2008 at 8:58 am
Hi there
We have a stored procedure like this :
SELECT @sql = 'SELECT * FROM dbo.[OPS Sepia$Sales Line] WHERE ([Document Type] = 1) AND (No_ =''' + @artid + ''') AND ([Return Shipment date] >= '''+@date+''') AND ([Shipment date] <= '''+@date+''') '
Ok, I already told programmer to get rid of the * and take the correct fields.
In this table there is an index on the field [Entry No]. This will not be part of the query.
On what should we build extra indexes ? On the select fields or on the where fields ?
Thx in advance !
El Jefe
JV
June 25, 2008 at 11:16 am
An index on the Where fields will allow for faster selects, but will still leave you with bookmark lookups (since it has to first find the rows by using the Where clause, then look up the data for them in the rest of the table).
An index with the Where fields in the main part of the index, and the Select columns in the include part of the index, will be even faster.
Either will cause insert/update/delete to be slightly slower. A few good indexes usually don't slow down transactions enough for it to matter, but it's worth keeping an eye on it.
So, which index solution is optimal for you will depend on how common it is to run that query, and whether creating a covering index will slow down the rest of the operations on the table too much, or will it be okay. (Covering index = index with Where/Join columns in main body of index, and remaining Select columns in Include portion.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 26, 2008 at 10:49 am
jvElecenter (6/25/2008)
Hi thereWe have a stored procedure like this :
SELECT @sql = 'SELECT * FROM dbo.[OPS Sepia$Sales Line] WHERE ([Document Type] = 1) AND (No_ =''' + @artid + ''') AND ([Return Shipment date] >= '''+@date+''') AND ([Shipment date] <= '''+@date+''') '
Ok, I already told programmer to get rid of the * and take the correct fields.
In this table there is an index on the field [Entry No]. This will not be part of the query.
On what should we build extra indexes ? On the select fields or on the where fields ?
Thx in advance !
El Jefe
Hi,
while obviously I don't know exactly what your proc is doing here, it does look as if you're building a dynamic SQL string to run. Any reason you can't rewrite it as something like;
SELECT
FROM
dbo.[OPS Sepia$Sales Line]
WHERE
[Document Type] = 1
AND
No_ = @artID
AND
[Document Type] = 1
AND
[Return Shipment date] = @date
AND
[Shipment date] = @date
which means you have an optimised query plan in cache for the proc?
June 27, 2008 at 2:46 am
Hi thx for reply
The only difference between your statement and ours is in the dates where you equal to.
We need the info until that date.
it's a stored procedure which is called upon within an asp web application.
thx
Jeffrey
JV
June 27, 2008 at 3:18 am
jvElecenter (6/27/2008)
Hi thx for replyThe only difference between your statement and ours is in the dates where you equal to.
We need the info until that date.
it's a stored procedure which is called upon within an asp web application.
thx
Jeffrey
Well - the key difference is that with your dynamic SQL approach, the optimiser has to generate a new query plan every time you run the sp. Apart from the time this takes - and the CPU resources it consumes, it also takes up more plan buffer memory and decreases the efficiency of how it's used. If you rewrite as a non dynamic proc, the plan is generated only once and reused each time the proc's run, meaning you should see a performance gain from doing it that way
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply