Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sivabsk2000
Starting Member
2 Posts |
 Posted - 2008-08-11 : 08:01:08
|
| Hi All,I request you to provide your valuable suggestions and tips to improve the performance of the SP I am using to retrive count of cards from a table, which some times throws Timeout error in front-end. It happens only production environment particularly when number of cards count exceeds 1 lac.Following is the SP call in another SP:EXEC @LCount = gemini.dbo.pboGetEmbossCount @pClientId,@pSubprogId,@pPackageId with recompileFollowing is the Query used with in the SP pboGetEmbossCount to get count:-DECLARE @bin_table table (StartCard numeric(19,0), EndCard numeric(19,0)) INSERT INTO @bin_table(StartCard, EndCard) SELECT StartCard = convert(varchar, bin) + '0000000000', EndCard = convert(varchar, bin) + '9999999999' FROM sub_program sp (nolock) INNER join cm_package_program cpp (nolock) on cpp.subprogid =sp.subprogid WHERE sp.SubProgID = @pSubprogId and cpp.packageid=@pPackageId and (sp.TrueAnonymous = convert(bit, 1) or sp.Preallocated = convert(bit, 1)) and sp.active = convert(bit,1) SELECT @LCount = count(ae.cardnum) FROM @bin_table binner join gemini.dbo.a_emboss ae with(NOLOCK, index = PK_A_EMBOSS) on ae.cardnum >= b.StartCard and ae.cardnum <= b.EndCard inner join gemini.dbo.g_account_client gac (nolock) on gac.pan = ae.pan and gac.clientid = @pClientid inner join gemini.dbo.a_emboss_status aes(NOLOCK) on aes.cardnum = ae.pan and aes.status in (0,1) where ae.subprogid = @pSubProgId and ae.packageid = @pPackageid and ae.assigned = convert(bit, 0) option (MAXDOP 1)/*, force order)*/ Is there anything else needs to be changed in the above query to improve the performance?Using of 'with recompile' option will actually increase the performance?Please advice.siva |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-11 : 10:28:51
|
| What does execution plan suggest? |
 |
|
|
sivabsk2000
Starting Member
2 Posts |
Posted - 2008-08-12 : 04:46:26
|
| Thank you for the reply. I could not get you exactly, execution plan just shows the cost for each table referenced. please tell me the exact information you need...siva |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-12 : 05:18:40
|
quote:
Originally posted by sivabsk2000
Thank you for the reply. I could not get you exactly, execution plan just shows the cost for each table referenced. please tell me the exact information you need...siva
i was asking whether you noticed any high cost query parts in your execution plan. like some table scans,.. |
|
|
|
|
|
|
|
|
Stored Procedure - Timeout error