Help optimising SQL Query

Hi,
I have a problem I would really appreciate help with. I am generating
dynamic SQL and need to optimise it. The specific example I am trying to
optimise looks like this:

SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE
(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2
WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)
AND (Data LIKE 'SIDE BY SIDE%' )) ))AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3
WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))

I was OK optimising it with just 2 criteria and changed:
SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE

(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND

(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2

WHERE T1.DataHeaderID = CT2.DataHeaderID AND (( Data LIKE 'church%' ))))

which took 26 seconds to using a derived table

SELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1

inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )
CT2

on T1.DataHeaderID = CT2.DataHeaderID

WHERE

(T1.EntityFieldID IN ( 31) AND (T1.Data LIKE '12BORE%' ))

and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.

My problem is I need to write code to generate the SQL for 1 to n criteria
and am struggling to write the query for more than 2

Best regards,

AndrewCompletely untested...(I have two lovely young ladies ripping up some
cardboard behind me which is about an annoying a noise as you can get,
so if it's wrong, you have my excuse. I started on this before they
starting on the cardboard).

I've tried to tidy up your code a bit in the hopes that this points you
in a better direction for solving the problem with n criteria. Maybe
someone could take this further.

SELECT
T1.DataHeaderId

FROM
TB_DataDetailText T1
LEFT JOIN TB_DataDetailText CT2
ON T1.DataHeaderId = CT2.DataHeaderId
LEFT JOIN TB_DataDetailText CCT3
ON CT2.DataHeaderId = CCT3.DataHeaderId

WHERE
T1.EntityFieldId = 31 AND
T1.Data LIKE '12Bore% AND
CT2.EntityFieldId = 34 AND
CT2.Data LIKE 'Side By Side%' AND
CCT3.Data LIKE 'Church%'

Ryan|||Hi Ryan,

Thanks very much. Tidying it up like that makes it much more straightforward
to see what I should do. I have it cracked now and yes, that does work and
blindingly fast (there was a bit of a whinge about quotes but that may have
been me and was easily fixed). It also lets me see a second way that I can
do it.

Thanks again,

Andrew|||No problem.

Ryan

Andrew wrote:
> Hi Ryan,
> Thanks very much. Tidying it up like that makes it much more
straightforward
> to see what I should do. I have it cracked now and yes, that does
work and
> blindingly fast (there was a bit of a whinge about quotes but that
may have
> been me and was easily fixed). It also lets me see a second way that
I can
> do it.
> Thanks again,
> Andrew

Help Optimising SP

HI All,
I am busy importing records from excel into a SQL table.
I am using:
insert into PLImport2 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\work\coke\Detailed P&L June RE.xls', 'SELECT * FROM
[Import1]')
The excle sheet has 49000 records in it, which normally takes about 20-22
seconds, which includes opening the excel file of course.
Which normally
I have an instead of trigger, so that I can replace certain column on input.
With the trigger running it took 1:43:20
Could someone see if this could be optimised. Or is there a better way of
doing things.
Thanks
RObert
PS Below is the table and sp. Please dont worry too much about the field
name, ie [1], as this is how it was created when come over from excel.
This acts as a staging table , and will be put into the proper table once
all the validation is taken care of.
Thanks
CREATE TABLE [dbo].[PLImport2] (
[RowID] float NULL,
[PF] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PFDesc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acc] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Product] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostingPeriod] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1] float NULL,
[2] float NULL,
[3] float NULL,
[4] float NULL,
[5] float NULL,
[6] float NULL,
[7] float NULL,
[8] float NULL,
[9] float NULL,
[10] float NULL,
[11] float NULL,
[12] float NULL,
[111] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[21] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[31] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[41] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[51] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[61] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[71] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[81] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[91] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[101] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[112] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[121] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[122] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[22] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[32] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[42] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[52] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[62] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[72] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[82] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[92] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[102] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[113] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[123] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[13] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[23] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[33] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[43] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[53] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[63] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[73] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[83] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[93] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[103] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[114] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[124] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[PLImport2_tri] ON [dbo].[PLImport2]
WITH EXECUTE AS 'dbo'
INSTEAD OF INSERT
AS
BEGIN
--Declare some variables that we will use in the cursor
Declare @.profitcenter varchar(50), @.Acc varchar(75), @.pf varchar(50),
@.oldAcc varchar(75), @.RowID int, @.cnt int
set @.cnt = 1
--Declare the cursor and fetch records from inerted table
DECLARE OneChange CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC
FOR SELECT RowID, PF, Acc FROM inserted
OPEN OneChange
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
--loop thour the curso performing task on each record
WHILE (@.@.FETCH_STATUS=0)
BEGIN
-- If there are no records inserted already, insert at least one
if (select count(*) from plimport2) = 0
begin
insert into plimport2 select top 1 * from inserted
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
end
--now select the last inserted record, by getting the Max of field F!
select @.pf = PF, @.oldAcc = Acc from plimport2 where RowID = (select
max(RowID) from plimport2)
--compare the results if null, replace with old value
if @.pf is null
begin
print 'PF is null'
end
else
begin
insert into plimport2 select * from inserted i where i.RowID=@.RowID
if @.profitcenter is null
set @.profitcenter = @.pf
else
begin
set @.pf = @.profitcenter
end
update plimport2 set PF=@.profitcenter where PF is null
if @.Acc is null
set @.Acc = @.oldAcc
else
begin
set @.oldAcc = @.Acc
end
update plimport2 set Acc=@.Acc where Acc is null
print 'Pf - '+@.pf+' Profic Center = '+@.profitcenter+' Dep = '+@.Acc+'
Status = '+str(@.@.FETCH_STATUS)
end
set @.cnt = @.cnt+1
FETCH NEXT FROM OneChange INTO @.RowID, @.profitcenter, @.Acc
print 'next Status = '+str(@.@.FETCH_STATUS)
end
ENDRobert,
I would consider another alternative:
1. create a staging table and populate it from Excel:
select * into staging from OPENROWSET
2. issue one set-based UPDATE followed by one set-based insert. For
instance, try this:
[url]http://sql-server-tips.blogspot.com/2006/06/mimicking-merge-statement-in-sql.html[
/url]|||Hi There,
You should give sample data ,DDL and required result to work on.
I think you may try this as Alexander suggested (I am giving SQL 2000
version)
Update those rows taht exists
Update tableX set amt=amt+(Select a from tableY where
tablex.PKID=tabley.PKID) ,col2=xy,
col3=xz
where exists(Select a from tableY where tablex.PKID=tabley.PKID)
insert those rows that doesnot exists
insert into tableX (col1,col2,col3) Select * from tabley where Not
exists(Select a from tableY where tablex.PKID=tabley.PKID)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Alexander Kuznetsov wrote:
> Robert,
> I would consider another alternative:
> 1. create a staging table and populate it from Excel:
> select * into staging from OPENROWSET
> 2. issue one set-based UPDATE followed by one set-based insert. For
> instance, try this:
> http://sql-server-tips.blogspot.com...ent-in-sql.html[
/color]

Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...