Query running slow - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query running slow

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 03:13:10

Hi Guys,

I have a query that is used as the basis for a SSRS report which has started to run for a long time, and causes my SQL server to max out at about 100%.

The SQL is as follows:


select
o.openitemno,
convert(varchar, o.itemdate, 103) as BilledDate,
localtransvalue = 
case 
when o.billpercentage <> 100 then
	case 
        when wh.transtype = 511 then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage) / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
		else convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
	end
else
	case
        when wh.transtype = 511 then convert(decimal(10, 2), wip_table.localtransvalue / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), wip_table.localtransvalue)
		else convert(decimal(10, 2), wip_table.localtransvalue) 
	end
end,
totalunits =
case
when o.billpercentage <> 100 then
	case
		when wh.transtype = 511 then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage) / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
		else convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
	end
else
	case
		when wh.transtype = 511 then convert(decimal(10, 1), wip_table.totalunits / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), wip_table.totalunits)
		else convert(decimal(10, 1), wip_table.totalunits) 
	end
end,
wip_table.wipcode,
c.irn,
e.abbreviatedname
from openitem o
inner join workhistory wh on wh.reftransno = o.itemtransno
inner join cases c on c.caseid = wh.caseid
inner join employee e on e.employeeno = wh.employeeno
inner join debtorhistory d on d.openitemno = o.openitemno and d.movementclass = 1
inner join name n on n.nameno = d.acctdebtorno
inner join (
select distinct(wh2.transno), wh2.wipcode, wh2.totalunits, wh2.localtransvalue
from workhistory wh2
inner join wiptemplate wp on wp.wipcode = wh2.wipcode and wp.wiptypeid = 'SERCHG'
where wh2.transtype in (400, 402, 406, 1000, 1003, 1001)
) as wip_table on wip_table.transno = wh.transno
where cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) >=  cast((str(year(@startDate)) + '/' + str(month(@startDate)) + '/' + str(day(@startDate))) as DATETIME )
and cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) <=  cast((str(year(@endDate)) + '/' + str(month(@endDate)) + '/' + str(day(@endDate))) as DATETIME )
and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end
and wh.transtype in (510, 511, 512, 516)
and o.status <> 9
order by o.itemdate asc, c.irn asc

This has been running fine in the past, but I imagine that there is a large amount of data to be returned. In this case the user supplies a date range to the report which encompasses a whole years worth of data.

I have rebuilt the indexes etc to see if this made a difference but I'm still having the same issue. I'm not that familiar with looking through execution plans so I thought whilst I do that someone may see a more efficient way of writing the query.

Any suggestions would be greatly appreciated!

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-26 : 03:23:01

Please Provide the PKey and index information for all the tables used in the Query.

Lets unLearn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-26 : 03:38:11

Your code in these segments may be the cause of the problem.
effectively you are coding in a request for table scans - poor performance.
what are you trying to achieve? optional parameters?

and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end

There are often better (performing) techniques than this....like below.
WHERE (@instatus is null or yourstatusfield=@instatus)

And with these below....just get the date values (in database and incoming parameters) into the correct datatypes before manipulating them. what you are doing is very "performance costly".

cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) >= cast((str(year(@startDate)) + '/' + str(month(@startDate)) + '/' + str(day(@startDate))) as DATETIME )
and cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) <= cast((str(year(@endDate)) + '/' + str(month(@endDate)) + '/' + str(day(@endDate))) as DATETIME )

I'd suspect you were accidently getting good (acceptable) performance until data volumes increased.

re execution plans in general, SCANS are one of your enemies.

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 03:39:48

The main two tables are WORKHISTORY and OPENITEM but its probably the WORKHISTORY table which is causing the issue. Ther SQL for the table is below:


USE [INPRO]
GO
/****** Object:  Table [dbo].[WORKHISTORY]    Script Date: 05/26/2010 08:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WORKHISTORY](
	[ENTITYNO] [int] NOT NULL,
	[TRANSNO] [int] NOT NULL,
	[WIPSEQNO] [smallint] NOT NULL,
	[HISTORYLINENO] [smallint] NOT NULL,
	[TRANSDATE] [datetime] NULL,
	[POSTDATE] [datetime] NULL,
	[TRANSTYPE] [smallint] NULL,
	[RATENO] [int] NULL,
	[WIPCODE] [nvarchar](6) NULL,
	[CASEID] [int] NULL,
	[ACCTENTITYNO] [int] NULL,
	[ACCTCLIENTNO] [int] NULL,
	[EMPLOYEENO] [int] NULL,
	[TOTALTIME] [datetime] NULL,
	[TOTALUNITS] [smallint] NULL,
	[UNITSPERHOUR] [smallint] NULL,
	[CHARGEOUTRATE] [decimal](11, 2) NULL,
	[ASSOCIATENO] [int] NULL,
	[INVOICENUMBER] [nvarchar](20) NULL,
	[FOREIGNCURRENCY] [nvarchar](3) NULL,
	[FOREIGNTRANVALUE] [decimal](11, 2) NULL,
	[EXCHRATE] [decimal](8, 4) NULL,
	[LOCALTRANSVALUE] [decimal](11, 2) NULL,
	[REFENTITYNO] [int] NULL,
	[REFTRANSNO] [int] NULL,
	[REFSEQNO] [int] NULL,
	[REFACCTENTITYNO] [int] NULL,
	[REFACCTDEBTORNO] [int] NULL,
	[REASONCODE] [nvarchar](2) NULL,
	[BILLLINENO] [smallint] NULL,
	[EMPPROFITCENTRE] [nvarchar](6) NULL,
	[CASEPROFITCENTRE] [nvarchar](6) NULL,
	[NARRATIVENO] [smallint] NULL,
	[SHORTNARRATIVE] [nvarchar](254) NULL,
	[LONGNARRATIVE] [ntext] NULL,
	[ASSOCLINENO] [smallint] NULL,
	[TRANSFERDETAIL] [int] NULL,
	[STATUS] [smallint] NULL,
	[MOVEMENTCLASS] [smallint] NULL,
	[COMMANDID] [smallint] NULL,
	[ITEMIMPACT] [smallint] NULL,
	[POSTPERIOD] [int] NULL,
	[VARIABLEFEEAMT] [decimal](11, 2) NULL,
	[VARIABLEFEETYPE] [smallint] NULL,
	[VARIABLEFEECURR] [nvarchar](3) NULL,
	[FEECRITERIANO] [int] NULL,
	[FEEUNIQUEID] [smallint] NULL,
	[GLMOVEMENTNO] [int] NULL,
	[QUOTATIONNO] [int] NULL,
	[EMPFAMILYNO] [smallint] NULL,
	[EMPOFFICECODE] [int] NULL,
	[VERIFICATIONNUMBER] [nvarchar](20) NULL,
	[LOCALCOST] [decimal](11, 2) NULL,
	[FOREIGNCOST] [decimal](11, 2) NULL,
	[ENTEREDQUANTITY] [int] NULL,
	[DISCOUNTFLAG] [decimal](1, 0) NULL,
	[NARRATIVE_TID] [int] NULL,
	[COSTCALCULATION1] [decimal](11, 2) NULL,
	[COSTCALCULATION2] [decimal](11, 2) NULL,
	[PRODUCTCODE] [int] NULL,
	[GENERATEDINADVANCE] [decimal](1, 0) NULL,
	[LOGUSERID] [nvarchar](50) NULL,
	[LOGIDENTITYID] [int] NULL,
	[LOGTRANSACTIONNO] [int] NULL,
	[LOGDATETIMESTAMP] [datetime] NULL,
	[LOGAPPLICATION] [nvarchar](128) NULL,
	[LOGOFFICEID] [int] NULL,
	[MATCHENTITYNO] [int] NULL,
	[MATCHTRANSNO] [int] NULL,
	[MATCHWIPSEQNO] [int] NULL,
	[MATCHEDTOOPENITEM] [bit] NULL,
	[MATCHEDFULLY] [bit] NULL,
	[MARGINNO] [int] NULL,
 CONSTRAINT [XPKWIPHISTORY] PRIMARY KEY CLUSTERED 
(
	[ENTITYNO] ASC,
	[TRANSNO] ASC,
	[WIPSEQNO] ASC,
	[HISTORYLINENO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1267] FOREIGN KEY([PRODUCTCODE])
REFERENCES [dbo].[TABLECODES] ([TABLECODE])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1267]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1321] FOREIGN KEY([RATENO])
REFERENCES [dbo].[RATES] ([RATENO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1321]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1325] FOREIGN KEY([ASSOCIATENO])
REFERENCES [dbo].[NAME] ([NAMENO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1325]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1503] FOREIGN KEY([POSTPERIOD])
REFERENCES [dbo].[PERIOD] ([PERIODID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1503]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1559] FOREIGN KEY([VARIABLEFEECURR])
REFERENCES [dbo].[CURRENCY] ([CURRENCY])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1559]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1560] FOREIGN KEY([FEECRITERIANO], [FEEUNIQUEID])
REFERENCES [dbo].[FEESCALCULATION] ([CRITERIANO], [UNIQUEID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1560]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1625] FOREIGN KEY([REFENTITYNO], [REFTRANSNO])
REFERENCES [dbo].[TRANSACTIONHEADER] ([ENTITYNO], [TRANSNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1625]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1626] FOREIGN KEY([REFENTITYNO], [REFTRANSNO])
REFERENCES [dbo].[TRANSACTIONHEADER] ([ENTITYNO], [TRANSNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1626]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_1627] FOREIGN KEY([ENTITYNO], [TRANSNO])
REFERENCES [dbo].[TRANSACTIONHEADER] ([ENTITYNO], [TRANSNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_1627]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_201] FOREIGN KEY([EMPLOYEENO])
REFERENCES [dbo].[NAME] ([NAMENO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_201]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_22] FOREIGN KEY([QUOTATIONNO])
REFERENCES [dbo].[QUOTATION] ([QUOTATIONNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_22]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_26A] FOREIGN KEY([EMPFAMILYNO])
REFERENCES [dbo].[NAMEFAMILY] ([FAMILYNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_26A]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_81506] FOREIGN KEY([MARGINNO])
REFERENCES [dbo].[MARGIN] ([MARGINNO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_81506]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_88] FOREIGN KEY([WIPCODE])
REFERENCES [dbo].[WIPTEMPLATE] ([WIPCODE])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_88]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [R_950] FOREIGN KEY([EMPOFFICECODE])
REFERENCES [dbo].[OFFICE] ([OFFICEID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [R_950]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1193] FOREIGN KEY([NARRATIVENO])
REFERENCES [dbo].[NARRATIVE] ([NARRATIVENO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1193]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1194] FOREIGN KEY([FOREIGNCURRENCY])
REFERENCES [dbo].[CURRENCY] ([CURRENCY])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1194]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1197] FOREIGN KEY([CASEID])
REFERENCES [dbo].[CASES] ([CASEID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1197]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1228] FOREIGN KEY([REASONCODE])
REFERENCES [dbo].[REASON] ([REASONCODE])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1228]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1238] FOREIGN KEY([EMPPROFITCENTRE])
REFERENCES [dbo].[PROFITCENTRE] ([PROFITCENTRECODE])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1238]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1239] FOREIGN KEY([CASEPROFITCENTRE])
REFERENCES [dbo].[PROFITCENTRE] ([PROFITCENTRECODE])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1239]
GO
ALTER TABLE [dbo].[WORKHISTORY]  WITH NOCHECK ADD  CONSTRAINT [RI_1252] FOREIGN KEY([ACCTENTITYNO], [ACCTCLIENTNO])
REFERENCES [dbo].[ACCOUNT] ([ENTITYNO], [NAMENO])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[WORKHISTORY] CHECK CONSTRAINT [RI_1252]

Is there an easy way to list the indexes?

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 03:43:59

The optional parameters are used on the report end. The options can either contain a value or be blank. For example, on the line -

and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end

The data returned could only be for one specific WIP code or every WIP code.

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 04:14:25

I think I may have found one slight issue with this. When I ran the following code on the test server it took 15 seconds to run:


declare @startDate datetime
set @startDate = '2009-01-01 00:00:00.000'
declare @endDate datetime
set @endDate = '2009-12-31 00:00:00.000'
declare @feeEarner varchar(3)
set @feeEarner = ''
declare @WipCode varchar(5)
set @WipCode = ''
declare @AcCode varchar(8)
set @AcCode = ''
declare @Country varchar(2)
set @Country = ''
select
o.openitemno,
convert(varchar, o.itemdate, 103) as BilledDate,
localtransvalue = 
case 
when o.billpercentage <> 100 then
	case 
        when wh.transtype = 511 then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage) / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
		else convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
	end
else
	case
        when wh.transtype = 511 then convert(decimal(10, 2), wip_table.localtransvalue / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), wip_table.localtransvalue)
		else convert(decimal(10, 2), wip_table.localtransvalue) 
	end
end,
totalunits =
case
when o.billpercentage <> 100 then
	case
		when wh.transtype = 511 then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage) / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
		else convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
	end
else
	case
		when wh.transtype = 511 then convert(decimal(10, 1), wip_table.totalunits / -1)
		when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), wip_table.totalunits)
		else convert(decimal(10, 1), wip_table.totalunits) 
	end
end,
wip_table.wipcode,
c.irn,
e.abbreviatedname
from openitem o
inner join workhistory wh on wh.reftransno = o.itemtransno
inner join cases c on c.caseid = wh.caseid
inner join employee e on e.employeeno = wh.employeeno
inner join debtorhistory d on d.openitemno = o.openitemno and d.movementclass = 1
inner join name n on n.nameno = d.acctdebtorno
inner join (
select distinct(wh2.transno), wh2.wipcode, wh2.totalunits, wh2.localtransvalue
from workhistory wh2
inner join wiptemplate wp on wp.wipcode = wh2.wipcode and wp.wiptypeid = 'SERCHG'
where wh2.transtype in (400, 402, 406, 1000, 1003, 1001)
) as wip_table on wip_table.transno = wh.transno
where cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) >=  cast((str(year(@startDate)) + '/' + str(month(@startDate)) + '/' + str(day(@startDate))) as DATETIME )
and cast((str(year(o.itemdate)) + '/' + str(month(o.itemdate)) + '/' + str(day(o.itemdate))) as DATETIME ) <=  cast((str(year(@endDate)) + '/' + str(month(@endDate)) + '/' + str(day(@endDate))) as DATETIME )
and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end
and wh.transtype in (510, 511, 512, 516)
and o.status <> 9
order by o.itemdate asc, c.irn asc

Yet on the Live server I get the following messages:

Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Msg 242, Level 16, State 3, Line 13
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any idea why this is happening? The copy of the database on the test server is the same, and I checked the date setting on both servers and they are also the same.

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 05:28:38

Ok so it looks like the issue with my query is with the bit where I'm checking the values supplied in the report input boxes:


and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end

Does anyone know of a better way to do this?

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 05:54:55

This is resolved!

I used the syntax suggested by AndrewMurphy for handling my input parameters.

Thanks for the help!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-26 : 06:54:39

dates should always be in yyyymmdd format, with no formatting.
this will allow SQL to manipulate with no "conversion errors"

good to hear some of my suggestions works.

If your execution plans still have SCANS (other than on 1 record tables) then do revert, because there may be other things we can suggest.

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-05-26 : 06:57:37

I actually changed the date part in the where clause to be:

where o.itemdate between @startDate and @endDate

I was able to do this after solving the earlier date issue. There may be some other things I should change but I'll go through the execution plan and let you know if I need any other guidance.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources