Increase Speed Of query

Question

Using SQL, SQL server manager 2008, c-sharp.net 4.0 and ms visual studio professional 2010:

I have been working on a main query for my program, and so far it works exactly as intended. The only Problem is the amount of time it takes to run.

some times it can take up to 3mins for around 2000 records.

I have been asked to get this query much faster, but in all honesty I'm not sure how I can.

Query is below, it uses a linked server and 4 tables. 3 on one server and the other is local.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[GetSalesBuyers]    Script Date: 03/29/2012   10:03:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers] 
@Acct varchar(255),
@Cdisc varchar(255),
@bcs varchar(255), 
@From date, 
@Too date
AS
SELECT i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName,
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

FROM   
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime], 
                            dbo.iLines.BinSeqNo, 
                            dbo.Customer.cdisc,
                            dbo.Customer.Bcs
                     FROM Autopart.dbo.iheads INNER JOIN  Autopart.dbo.iLines ON 
                     Autopart.dbo.Iheads.document = autopart.dbo.iLines.document
                     INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct 
                     = Autopart.dbo.customer.keycode
                     GROUP By dbo.iHeads.acct,
                            dbo.iHeads.name,
                            dbo.iLines.Document,
                            dbo.iLines.Part,
                            dbo.iLines.Pg,
                            dbo.iLines.Qty,
                            dbo.iLines.unit,
                            dbo.iHeads.[DateTime],
                            dbo.iLines.BinSeqNo,
                            dbo.Customer.cdisc,
                            dbo.Customer.bcs
                      ') i
left JOIN
dbo.NEWPareto
ON 
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part 
left JOIN
dbo.MyPgTable 
ON  
 i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]

WHERE

 (i.[DateTime] BETWEEN @From AND @Too)

 AND (@Cdisc > 29 OR i.cdisc = @Cdisc)

 AND(@Acct = '0' 
 OR (@Acct = '1659%' AND i.Acct not Like @Acct) 
 OR (@Acct = '1557%' AND i.Acct Like @Acct)
 OR (@Acct = '18731%' AND i.Acct not Like '1873%' AND i.Acct not Like '1432%') 
 OR (@Acct != '1659%' AND i.Acct Like @Acct)) 

 AND(@bcs = '0' OR i.bcs != @bcs)

 AND i.pg != '60'
 AND i.pg != '61'
 AND i.pg != '62'

 GROUP BY i.Acct,
   i.Name, 
   i.Document, 
   i.Part, 
   i.Qty, 
   i.Unit, 
   dbo.NEWPareto.Pareto, 
   i.pg,
   dbo.MyPgTable.PgName, 
   i.[DateTime],
   i.BinSeqNo,
   i.cdisc,
   i.bcs

As you can see its pretty long but works fine, is there any way to drastically increase the speed? or is working with linked servers the problem?

Thanks to all of you for Helping me with this Question, I would love to select you all for the right answer, but I cant, so instead Up votes for all!

This might be a question better suited on [Code Review](http://codereview.stackexchange.com/) or [Database Administrators](http://dba.stackexchange.com/) — musefan, Mar 29 '12 at 09:15
Might be worth looking at any indexes and keys you have on your tables; you may be able to create more efficient indexes to help speed things up. — JTeagle, Mar 29 '12 at 09:16
Ill give those sites a try, was hopping my query had a part that was overlooked that could be made much quicker, hence why I asked here. — lemunk, Mar 29 '12 at 09:21
It seems like there is a lot in the where clause that actually can be in the openquery call. That should limit the data you are pulling from the linked server. — Mufaka, Mar 29 '12 at 09:24
mufaka, thats exactly i was hoping for, expect the where clause is mainly full of parameters, and i hear that they cannot be sent through to the open query unfortunatly, could you advise? — lemunk, Mar 29 '12 at 09:25
@StevenSmith Have a look at http://stackoverflow.com/questions/3378496/including-parameters-in-openquery — Mufaka, Mar 29 '12 at 09:29

score 3 · Accepted Answer · answered Mar 29 '12 at 09:20

3

Left Joins always make a query a lot slower, doing this across multiple databases will make it even slower.

There is also a Group by in the OpenQuery and again one at the end. Maybe replacing the OpenQuery with a temporary table (with an index on the column i.parts and i.pg) would speed things up.

answered Mar 29 '12 at 09:20

Lex

879
3
16
27

+1 for recommending use of a temporary table as a replacement for cross database joins – Seph Mar 29 '12 at 09:25
well spotted, i cant actually remember why i put that in the open query. i just took it out and using the same paramters i reduced the time by over 1min20 seconds.....wow – lemunk Mar 29 '12 at 09:36
also ill have a look at using temp table – lemunk Mar 29 '12 at 09:37

score 2 · Answer 2 · answered Mar 29 '12 at 09:15

2

AND i.pg != '60' AND i.pg != '61' AND i.pg != '62'

use i.pg not in ('60','61','62')

If you have frequently used parameters then try to use sql plan guides

answered Mar 29 '12 at 09:15

Darshan

121
1
1
8

trust me to forget the sodding basics, yer totally forgot about "not in" ty – lemunk Mar 29 '12 at 09:17
I don't think changing this will have any meaningful affect on performance – Seph Mar 29 '12 at 09:28
These values should indeed be put together, but why are they filtered out at the end? and not in the OpenQuery where they should be much easier to be filtered out. – Lex Mar 29 '12 at 09:38
true I cant see the difference in performance, but I should be using it instead of multiple AND gates, my bad for bad coding practice hehe – lemunk Mar 29 '12 at 09:39
originally they where filtered using the pareto table that was local, but i did make changes and now u mention it i could put them in the open query.BUT wouldnt that put more strain on the server rather than my local. (remote server is slow, local server is very fast). would this make a big difference? – lemunk Mar 29 '12 at 09:42

score 1 · Answer 3 · answered Mar 29 '12 at 09:40

Working with linked servers is best avoided, it plays havoc with the Temp DB and can not only cause performance issues with the queries you are running, but will impact the DB performance as a whole. Since you are working with SQL-Server-2008, I'd recommend instead of executing the cross server query within the procedure, instead pass a table variable to the procudure.

To pass a table variable to a stored procedure you first need to define the table structure as a new type. e.g.:

CREATE TYPE SACBAutoTable AS TABLE 
(    Acct    INT NOT NULL, 
     Name    VARCHAR(50) NOT NULL 
     ...etc
)

Then you can add this to the stored procedure definition. e.g.

ALTER PROCEDURE [dbo].[GetSalesBuyers] 
      @SABCAutoTable  SACBAutoTable READONLY, 
      @Acct           VARCHAR(255),
      @Cdisc          VARCHAR(255),
      etc

Further on in the query you can then change

SELECT .... FROM OPENQUERY(

to

SELECT .... FROM @SABCAutoTable ...

Finally to call the procedure from c#:

SqlDataAdapter adapter = new SqlDataAdapter(YourCrossServerQuery, YourLinkedServerConnectionString);
DataTable table = new DataTable();
adapter.Fill(table);

using (SqlConnection connection = new SqlConnection(yourMainServerConnectionString))
{
    using (SqlCommand command = new SqlCommand(YourSP, connection))
    {
        connection.Open();
        command.Parameters.Add(new SqlParameter("@YourTableVariable", table));
        connection.Close();
    }
}

More information on passing table variables to stored procedures can be found here http://msdn.microsoft.com/en-us/library/bb510489.aspx

If the performance of this is still slow then start looking at indexes, and other general tidying up of the query such as changing AND != '60' AND != '61' to AND NOT IN ('60', '61')

Increase Speed Of query

3 Answers3