pass @param too OpenQuery - 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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

pass @param too OpenQuery

Author

Topic

lemunk
Starting Member

9 Posts

Posted - 2011-11-23 : 04:46:46

Hi guys,
Im trying to pass my paramters to my open querys.

reason for this is due to each table being very VERY large.
i need to filter out as much as possible.
atm i was just using a where clause at the very bottom,
total running time to get 6 records was 1min38 seconds......
So i need to drasticly improve my speed.
One way is by filtering heres my code so far:


USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[GetSalesPareto]    Script Date: 11/23/2011 09:12:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesPareto] 
	@acct varchar(10), 
	@From varchar(20), 
	@Too varchar (20)
AS
SELECT ih.Acct,
	   ih.Name, 
	   iL.Document, 
	   iL.Part, 
	   iL.Qty, 
	   iL.Unit, 
	   dbo.NEWPareto.Pareto, 
	   dbo.NEWPareto.PG, 
       ih.DateTime,
       DATEPART(year,ih.DateTime) AS Year_1,
       DATEPART(Month, ih.DateTime) AS Month_1
FROM   
OPENQUERY(SACBAUTO, 'SELECT Document,part,qty,unit FROM AutoPart.dbo.iLines WHERE ih.DateTime BETWEEN + @From + AND @Too') iL 
	INNER JOIN
OPENQUERY(SACBAUTO, 'SELECT acct,name,[datetime], Document,DATEPART(year, DateTime),DATEPART(Month, DateTime) FROM AutoPart.Dbo.iheads WHERE DateTime BETWEEN @From  AND @Too) AND
	 acct = @acct') ih 
ON	   
	iL.Document collate SQL_Latin1_General_CP1_CI_AS = ih.Document 
INNER JOIN
    dbo.NEWPareto  
ON 
	iL.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
WHERE
	 (ih.DateTime BETWEEN @From  AND @Too) AND
	 acct = @acct

as you can see the very bottom is my original where clause, i need my open querys to filter suing the same parameters.
can you guys help?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 04:52:22

Doing a join on openqueries is likely to cause problems (imho)
I would do this by getting data into temp tables using
insert #a
exec (@sql) at SACBAUTO

then you can do the join on the linked server or get the two datasets into temp tables and do the join locally (which is what your join would probably do at the moment). It just becomes a matter of populating the @sql variable which is then easy to test.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lemunk
Starting Member

9 Posts

Posted - 2011-11-23 : 05:33:32

yes but, this query will be run multi times, if i did this sepereate i would need to run temp tables mulitpul times then join mulitpul times (srry i cant spell). wouldnt this be ineffecient? like i said the data from the open querys are huge so i would need to 1st pass the params to these and create the temp table, then i would need to run another query with an extra param to then put into my datagridview in my program.
Im not sure but it seems this seems not to be very dynamic? Im not totally sure just it would seem that way to me. could you explain in a lil more detail why this would be better?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 06:45:25

Have a look at the query plan?
I suspect it would extract the data from the two openqueries and join on your local server. If you extracted the two dtasets to temp tables you are giving yourself more flexibility.

If possible I would do the join on the remote server and transfer the minimum data (that's if it can handle the join e.g. collation you need).

If you want to do the join on the remote server then something like
select @sql =
'
select ....
from
(SELECT Document,part,qty,unit FROM AutoPart.dbo.iLines WHERE ih.DateTime BETWEEN ''' + convert(varchar(8),@From,112) + ''' AND ''' + convert(varchar(8),@Too,112) + ''') iL
join
(SELECT acct,name,[datetime], Document,DATEPART(year, DateTime),DATEPART(Month, DateTime) FROM AutoPart.Dbo.iheads WHERE DateTime BETWEEN ''' + convert(varchar(8),@From,112) + ''' AND ''' + convert(varchar(8),@Too,112) + ''') AND
acct = convert(varchar(20),@acct) ih
ON
iL.Document collate SQL_Latin1_General_CP1_CI_AS = ih.Document
'
insert #a
exec (@sql) at SACBAUTO

Then use that for the rest of the query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lemunk
Starting Member

9 Posts

Posted - 2011-11-23 : 07:53:01

well i just did the join between iheads and ilines tables on the remote server, then passed that info down to my local server and ran a join to NEWpareto. this considerbly reduced the time from roughly 1min20-40 to 20-30, so over a minute taken off.
It would be much better if i could filter again but on the remote sever via passing params to it.
heres the new code so far.

[CODE]
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[GetSalesParetotemp] Script Date: 11/23/2011 12:50:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesParetotemp]
@acct varchar(10),
@From varchar(20),
@Too varchar (20)
AS
SELECT i.Acct,
i.Name,
i.Document,
i.Part,
i.Qty,
i.Unit,
dbo.NEWPareto.Pareto,
dbo.NEWPareto.PG,
i.DateTime
--DATEPART(year,i.DateTime) AS Year_1,
--DATEPART(Month, i.DateTime) AS Month_1
FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
dbo.iHeads.name,
dbo.iLines.Document,
dbo.iLines.Part,
dbo.iLines.Qty,
dbo.iLines.unit,
dbo.iHeads.DateTime
FROM Autopart.dbo.iheads INNER JOIN Autopart.dbo.iLines ON
Autopart.dbo.Iheads.document = autopart.dbo.iLines.document') i
INNER JOIN
dbo.NEWPareto
ON
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
WHERE
(i.DateTime BETWEEN @From AND @Too) AND
acct = @acct
[/CODE]
so as you can see i need to add the params into the openquery (althoug i hear u cannot directly use it like this). by creating the open query as a paramter? seems alot to ask but could you show me how it would look and Possibly add a few comments for to reference.
this would help me a great deal as i could then understand for future references as im practically brand new to SQL hehe.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 08:12:00

Why are you against getting the data into a temp table as I suggested? Then you can see whether it is the remote query or the local join that is taking the time and can also index and preprocess the copied data.
It's much easier and more maintainable than trying to make a single query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lemunk
Starting Member

9 Posts

Posted - 2011-11-23 : 09:35:02

programming terms it would be better to call only one query not call 2 seperate querys. if i was to make a temp table, how would this then be updated? (never made a temp table). would i need to create, then update but delete contents before update? or does it only exist aslong as the procedure is running?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 09:59:37

It exists as long as the sp is running and is automatically droped at the end.
You create it in the same way as a permanent table - just prefix the name with #. Each connection gets its own version of the temp table.

create table #mytbl (Part varchar(20), DateTime datetime, Acct varchar(20), ...)

insert #mytbl
exec (@sql) at SACBAUTO

You could also use a table variable but I suspect this isn't going to be quich enough for that to be benificial anyway.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources