SQL Query Performance

[WarlockSoul]

Dear Guru's,

I'm creating a store procedure, in MS SQL 2k, that will run on a set date each week and e-mail the results.

If I explicitly declare the the start and finish dates the query returns the results in just over 1 minute (eg. VBAK.ERDAT Between '20050124' and '20050128')

I have declare some variables to try make the store procedure intuitive eg. on Monday grab the data from the previous weeks orders (eg. VBAK.ERDAT Between @sMyStart and @sMyFinish) and the query now takes 7 minutes.

I've looked thru the Online Book and can't seem to find anything that helps. VBAK.ERDAT is defined as Varchar(8) in the database.

Below is the code.

Thanks for any help.

Set NoCount On
Set DateFirst 1

Declare @sStartDate DateTime, @iDaySteps Int, @sAgent Varchar(20), @sMyStart Varchar(8), @sMyFinish Varchar(8)

Set @sStartDate = GetDate() - (DatePart(dw, GetDate()) + 6)
Set @iDaySteps = 1

Set @sMyStart = Convert(Varchar(8), @sStartDate, 112)
Set @sMyFinish = Convert(Varchar(8), @sStartDate + (DatePart(dw, @sStartDate) + 3),112)

Delete From T151T
Delete From tempOrders

Insert Into T151T
Select * From OpenQuery
(WMAN,
'Select * From T151T Where MANDT In (''400'') and SPRAS In (''E'') Order By T151T.KTEXT'
)

Insert Into tempOrders
Select
T151T.KTEXT,
VBAK.ERDAT,
Count(Distinct(VBAK.VBELN)),
Sum(VBAP.NETWR)

From VBAP
Inner Join VBAK on (VBAP.MANDT = VBAK.MANDT and VBAP.VBELN = VBAK.VBELN)
Inner Join (Select * From OpenQuery
(WMAN,
'Select KNVV.KUNNR as ''kunnr'', KNVV.KDGRP as ''kdgrp'' From KNVV'
)
) knvv On (VBAK.KUNNR = knvv.kunnr)
Inner Join (Select * From OpenQuery
(WMAN,
'Select T921.VBELN as ''vbeln'', T921.POSNR as ''posnr'' From T921'
)
) t921 On (Right(VBAP.VBELN,6) = Right(t921.vbeln,6) and VBAP.POSNR = t921.posnr)
Inner Join T151T on (knvv.kdgrp = T151T.KDGRP)

Where VBAK.AUART In ('TA') and VBAK.ERDAT Between @sMyStart and @sMyFinish and T151T.KDGRP Not In ('99')
Group By T151T.KTEXT, VBAK.ERDAT
Order By T151T.KTEXT, VBAK.ERDAT

[Krzemo]

enable SHOWPLAN_TEXT and save output to files (one for "before" and one for "after"). Compare those plans (and post it on codeguru :) ).

Best regards,
Krzemo.

[WarlockSoul]

Hi Krzemo,

Many thanks for the ShowPlan_Text On.

It certainly opened my eyes to how the the queries are being processed. Will certainly use it more often.

Looking at the 'Plans' with the explicitly declared dates vs. the variables I notice that the second query had a 'many-to-many' join on two tables/fields.

T151T.KTEXT = VBAK.ERDAT.

I can't see why this was putting a join on these two fields but these seem to be the culprits that where slowing down the query.

T151T.KTEXT is a text table, in SAP, for the Sales Office field and is defined as a Varchar(20).
VBAK.ERDAT is a creation date, in SAP, for orders and is defined as a Varchar(8).

I can only think that I have a few tables that sit on my server(on my client machine), that grab data from the live server before being archived, that I haven't defined currently.

The best solution I found was to declare the @sMyStart and @sMyFinish variables as DateTime (not varchar(8)) and then using the convert function to transfer these varialbles into SAP date formats in the Where clause.

I also created a derived OpenQuery on T151T, rather that using the static table from my server, and now get the results in 45 sec.

I've attached the before and after plans. If you spot anything else I might have missed I would certain be grateful.

[Krzemo]

1) try to use TRUNCATE instead of DELETE

2) use "Select YourColumnlistHere From OpenQuery ..) That way U transfer only important columns (not all as in "*") which can speed up query.
3) use "=" insead of "IN()"
4) avoid sorting while inserting - it will slow down inserts but your database cannot benefit from it. Better not to sort and to create index after import .

Looking at the 'Plans' with the explicitly declared dates vs. the variables I notice that the second query had a 'many-to-many' join on two tables/fields.

T151T.KTEXT = VBAK.ERDAT. No U just make sorting on that columns....
Anyway: your queries don't use any indexes...
How large those tables are?
Maybe if U drop\create some indexes it would speed up a bit?
Why U create table T151T, isn't it better to join it dynamically? Than U can add "WHERE MANDT In (''400'') and SPRAS In (''E'') AND KDGRP <>'99'"

Best regards,
Krzemo.

[WarlockSoul]

Hi Krzemo,

Many thanks for the hints.

The 'Truncate' function has speed up the query dramically. (Thanks).

I had already thought using the 'Select * From OpenQuery' was a waste of resources and as you suggested have only selected the appropriate fields.

Is there a performance between using "=" instead of "In ()"?

The biggest table that resides on my machine has just under 7 million records.

Anyway: your queries don't use any indexes...

I have clustered indexes on the main tables on my machine. I'm uncertain by what you mean. If there something else I'm missing in my query?

The query now runs in just over a second, brilliant!