Post reply

SQL Server Query times out but same query in Access does not

  • September 24, 2008 at 5:59 am

    #213019

    We are migrating from Access to SQL Server 2005. Currently we run a pass-thru query against a remote sql server database table with over 65 million items. The Access query runs in 5 mins.

    The query requires date parameters to filter the data returned. The T-SQL openquery statement will not accept parameters into the SQL string, but a look around the internet led me to the following code:

    declare @Activity_Start_Date datetime

    declare @Activity_End_Date datetime

    SELECT @Activity_End_Date = CAST ( '01-' + DATENAME (month, GETDATE()) + '-' + DATENAME (year, GETDATE()) AS datetime)

    SELECT @Activity_Start_Date = DATEADD(month, -13, @Activity_End_Date)

    select min(PA.change_date), PA.ticket_nbr, 'X1ECSGBMIDDLEWARE' AS Assignment

    FROM [i]linkedservername[/i].Peregrine.dbo.scActivity PA

    WHERE (PA.change_date >= @Activity_Start_Date AND PA.change_date < @Activity_End_Date AND PA.change_desc LIKE '%abcefghijklmnopqrst%')

    GROUP BY PA.ticket_nbr

    The execution plan shows this is 100% run on the remote server, but it still timed out after 10 mins

    Any idea (a) why it runs more slowly than the Access pass-thru?

    (b) how I can write something that will work?

    Changes to the structure and/or indexing of the remote server are not an option

  • September 24, 2008 at 7:50 am

    #876361

    if your target table realy has > 65M recs, then *LIKE* part [BOLDED] in your query

    WHERE (PA.change_date >= @Activity_Start_Date AND PA.change_date < @Activity_End_Date AND PA.change_desc LIKE '%abcefghijklmnopqrst%')

    ..will make it very slowly. Try to run the query without LIKE to see what difference it makes. If it does [it should] than try to figure a more performant way of filtering. I found from experience that CLR scalar functions designed for search perform considerably faster than LIKE operator when used for searching in the middle of the string.

  • September 24, 2008 at 8:09 am

    #876385

    Scalar Value Functions are new to me. I just googled them and it's clearly something I'm not going to fix today!

    I've managed to get the query to run by altering the remote query timeout setting in SQL server, but it took 30 mins instead of 5 on Access.

    The report runs once a month overnight, so we can live with this for a few months until I can get time to learn new techniques.

    Thanks for your help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply