Parameter in Select Qry in Stored Procedure

Question

Parameter in Select Qry in Stored Procedure

rohini-145252

Old Hand

Points: 328
More actions
September 21, 2006 at 6:22 am

#114584

When i pass parameters to the where condition of a select qry it takes more time to run, than the same query when i give default values in the where clause.
For Eg:
The qry no 1 takes more time than query no 2. The execution plan also differs.
Query No 1:
declare @ua varchar(10)
set @ua='ua'
select * from bill_ledger where jrlid=@ua
Query No 2:
select * from bill_ledger where jrlid='ua'
This is just a sample but my query seems to be more complicated.
Any help appreciated.
Thanks
Rohini

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

It should take same time, it would be more clear if you can post the exact the script or atleast something near to what your doing.

Prasad Bhogadi
www.inforaise.com

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

It could be a data type precedence problem.

eg If jrlid is a char it will automatically to promoted to a varchar and any indexes on jrlid will not be used. You will be able to get around this be declaring @ua as char(10).

If that is not the problem, then looking at the different execution plans should point you in the right direction.

rohini-145252 Old Hand Points: 328 More actions · Answer 3

The parameters are

@GrpCompID varchar(15),

@StDt datetime,

@EdDt datetime,

@LedgerIDsL varchar(500),

The Actual query i am using is

SELECT Trans_Detail.CompanyID, Trans_Detail.Finyear, Trans_Detail.LedgerID, Trans_Header.HORefNo,

Trans_Detail.JrlID, Trans_Detail.TrnNo, Trans_Detail.TrnSNo, Trans_Detail.TrnDate, Trans_Detail.AmtD, Trans_Detail.AmtC,

Trans_Detail.HOCode, Trans_Header.Trnmmyy, Trans_Header.Narration, Trans_Header.BillNo, Trans_Header.BillDate,

Trans_Header.Posted, Trans_Header.PostedNo, Trans_Header.PostedDate, Trans_Header.InstrumentType,

Trans_Header.InstrumentNo, Trans_Header.InstrumentDate, Trans_Header.EffectiveDate, Trans_Header.postedJrlID,

Trans_Header.Vtype, Trans_Header.ModuleName, Journal.JrlName, Journal.Prefix

FROM Trans_Detail INNER JOIN

Trans_Header ON Trans_Detail.CompanyID = Trans_Header.CompanyID AND Trans_Detail.Finyear = Trans_Header.Finyear AND

Trans_Detail.JrlID = Trans_Header.JrlID AND Trans_Detail.HOCode = Trans_Header.HOCode AND

Trans_Detail.TrnNo = Trans_Header.TrnNo INNER JOIN

Journal ON Trans_Detail.CompanyID = Journal.CompanyID AND Trans_Detail.Finyear = Journal.FinYear AND Trans_Detail.JrlID = Journal.JrlID

WHERE (Trans_Header.EffectiveDate >= @StDt) AND (Trans_Header.EffectiveDate <= @EdDt) AND (Trans_Header.Posted = 1) AND

Trans_Detail.LedgerID = @LedgerIDsL AND (Trans_Header.DeletedTrn=0) and (Trans_Header.Companyid in (Select CompanyID from Company Where GroupCompanyID =@GrpCompID))

When i use the values directly instead of the parameters,

SELECT Trans_Detail.CompanyID, Trans_Detail.Finyear, Trans_Detail.LedgerID, Trans_Header.HORefNo,

Trans_Detail.JrlID, Trans_Detail.TrnNo, Trans_Detail.TrnSNo, Trans_Detail.TrnDate, Trans_Detail.AmtD, Trans_Detail.AmtC,

Trans_Detail.HOCode, Trans_Header.Trnmmyy, Trans_Header.Narration, Trans_Header.BillNo, Trans_Header.BillDate,

Trans_Header.Posted, Trans_Header.PostedNo, Trans_Header.PostedDate, Trans_Header.InstrumentType,

Trans_Header.InstrumentNo, Trans_Header.InstrumentDate, Trans_Header.EffectiveDate, Trans_Header.postedJrlID,

Trans_Header.Vtype, Trans_Header.ModuleName, Journal.JrlName, Journal.Prefix

FROM Trans_Detail INNER JOIN

Trans_Header ON Trans_Detail.CompanyID = Trans_Header.CompanyID AND Trans_Detail.Finyear = Trans_Header.Finyear AND

Trans_Detail.JrlID = Trans_Header.JrlID AND Trans_Detail.HOCode = Trans_Header.HOCode AND

Trans_Detail.TrnNo = Trans_Header.TrnNo INNER JOIN

Journal ON Trans_Detail.CompanyID = Journal.CompanyID AND Trans_Detail.Finyear = Journal.FinYear AND Trans_Detail.JrlID = Journal.JrlID

WHERE (Trans_Header.EffectiveDate >= '04/01/06') AND (Trans_Header.EffectiveDate <= '09/21/06) AND (Trans_Header.Posted = 1) AND

Trans_Detail.LedgerID ='1626' AND (Trans_Header.DeletedTrn=0) and (Trans_Header.Companyid in (Select CompanyID from Company Where GroupCompanyID ='SKML'))

The query seems to be faster and shows different execution plans too.

There is no datatype problem.

Thanks.

Rohini

rohini-145252 Old Hand Points: 328 More actions · Answer 4

rohini-145252

Old Hand

Points: 328

September 21, 2006 at 9:50 pm

#661899

Any quick help appreciated .

maddogs SSCrazy Points: 2474 More actions · Answer 5

This subject has been posted before in this forum and I am not sure why this works this way, but as first thing in your procedure declare some local variables identical to the parameters (except with different names of course), assign the parameter values to the corresponding local variables, and use the local variables in the WHERE clause. Seems to provide the optimizer with the hints it is looking for to arrive at the most efficient execution plan.

maddog

rohini-145252 Old Hand Points: 328 More actions · Answer 6

maddog I have tried even this. But this does not work.Any other suggestions?

maddogs SSCrazy Points: 2474 More actions · Answer 7

Well if the local variable usage doesn't fix it then you do have a real head-scratcher. I do however see a couple of places where you could change things which might narrow down where the problem is but not necessarily fix it.

You may already have tried this, but using the same local variable approach I mentioned, start with the first date (@StDt) and use the local variable in it while leaving the others hard-coded. Run it and check the execution speed and query plan to see if it changes. Do this with the rest of the variables one at a time, the idea being to find out exactly which variable is causing the less efficient query plan. Once you know this then you will be be better armed to resolve it.

You might try changing the date comparison statement as well so that it uses a BETWEEN operator instead of the '>=' and '<=' operator pair like (Trans_Header.EffectiveDate BETWEEN @StDt AND @EdDt). Do this after your stepping thru the first analysis above so you don't alter your benchmark execution plan while you're trying to troubleshoot.

Are you sure the @LedgerIsL parameter needs to be length of 500? Looks like it might be a numeric ID. Try cutting it down to varchar(10) for a test and run it with the local variables in the where clause.

Sorry I'm not much help beyond this on this problem because it is indeed a strange one and I'm out of ideas. We'd need to be able to run the scripts against a real data source to play with different arrangements of joins, possibly replacing the 'IN' clause where your looking for a Trans_Header.CompanyID within a group of company codes with a join against the trans header.

maddog

Sergiy SSC Guru Points: 110208 More actions · Answer 8

What is datatype definition for column Trans_Detail.LedgerID ?

You compare it to @LedgerIDsL which is varchar(500). If column type is shorter than varchar(500) SQL Server will implicitly convert it to varchar(500) . That means no indexes to be used because you match not indexed values but results of conversion.

Despite you say "There is no datatype problem" I believe there is.

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Yes looks for the "implicit_convert" keyword in the query plan. Might not seem like a problem but it often can become one.

rohini-145252 Old Hand Points: 328 More actions · Answer 10

Thanks for ur help.

But There is no data type problem @ all.

The @LedgersID.. param is a varchar field and here i pass comma separated ledgerIDs. So i use varchar(500).

Please help.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Varchar ( - what?)

There is no datatype "varchar". There are varchar(20), varchar(50), varchar(100), varchar(500), varchar(1000).

What is datatype of your column LedgersID?

_____________
Code for TallyGenerator

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 12

You mean to say, you save the LedgersID as Comma Separated values in the column or do you pass the comma separated values to SP through LedgersID. You are using a '=' for comparing the parameter value with the column value, so I am little confused as to what you are doing.

Prasad Bhogadi
www.inforaise.com

rohini-145252 Old Hand Points: 328 More actions · Answer 13

LedgerID 's datatype in table is varchar(10).

I use 'like' in the query so i pass comma separated values to the parameter.so i declared as varchar(500). I did not specify the like here in the eg. query. because the same problem occurs when i use 'like' or '='.

rohini

Sergiy SSC Guru Points: 110208 More actions · Answer 14

So, there is implicit conversion.

And there is datatype issue indeed.

But not only.

Matching to comma separated list does not work the way you made it.

You need to use "split" function to convert your list to table and join that table to your query.

I just posted my version of split function yesterday.

You may use it. But DON'T FORGET TO CONVERT VALUES RETURNED BY THAT FUNCTION TO VARCHAR(10) to avoid implicit conversion of values in permanent table.

Good luck.

_____________
Code for TallyGenerator