Post reply

Remote Query Timeout - min() vs. max()

  • February 26, 2009 at 3:05 pm

    #135124

    Hi All,

    I've got a couple of queries that run on a linked server. We just upgraded the SQL Server to 2005 from 2000, and since then one of the two queries that are routinely run, has failed. I think I've traced it back to a configuration setting called "remote query timeout" which apparently defaults to 600 (10 minutes). What's peculiar is that the exact same query running with the min() function runs in 42 seconds, while the max() query times out at the 10 minute setting. What is so special about a max() query that it should take so much longer?

    Any help would be appreciated. Thanks.

    Here's the code format for the max() corellated subqueries, which is the same

    format as the [faster] min code:

    TRUNCATE TABLE MYDB..CompanyLastPurchase;

    GO

    SET ROWCOUNT 0;

    INSERT INTO MYDB..CompanyLastPurchase(CompanyID, CompanyLastPurchDate, CompanyLastPurchNetTotal)

    select c.CompanyID as CompanyID,

    REPLACE(CONVERT(varchar(20), d.datedesc, 101), '-', '/') as CompanyLastPurchDate,

    sum(f.sales) as CompanyLastPurchNetTotal

    from REMOTESERVER.REMOTEDB1.dbo.transactionsdetailfact f with(nolock)

    JOIN REMOTESERVER.REMOTEDB1.dbo.DateDim d with (nolock) on d.datekey = f.datekey

    JOIN REMOTESERVER.REMOTEDB1.dbo.InvoiceTypeDim it with (nolock) on f.invoiceTypeKey=it.InvoiceTypeKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.SalesChannelDim sc with(nolock) on sc.SalesChannelKey = f.SalesChannelKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.IndividualDim i with (nolock) on i.IndividualKey = f.IndividualKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.CompanyDim c with (nolock) on c.CompanyKey = i.CompanyKey

    where d.datedesc =

    (select max(d1.datedesc)

    from REMOTESERVER.REMOTEDB1.dbo.transactionsdetailfact f1 with(nolock)

    JOIN REMOTESERVER.REMOTEDB1.dbo.DateDim d1 with (nolock) on d1.datekey = f1.datekey

    JOIN REMOTESERVER.REMOTEDB1.dbo.SalesChannelDim sc1 with(nolock) on sc1.SalesChannelKey = f1.SalesChannelKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.IndividualDim i1 with (nolock) on i1.IndividualKey = f1.IndividualKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.CompanyDim c1 with (nolock) on c1.CompanyKey = i1.CompanyKey

    JOIN REMOTESERVER.REMOTEDB1.dbo.InvoiceTypeDim it1 with (nolock) on f1.InvoiceTypeKey = it1.InvoiceTypeKey

    where c1.CompanyKey=c.CompanyKey and

    sc1.SalesChannel='Direct' and

    sc1.SalesChannelType ='Internal' and

    f1.sales >0 and

    it1.InvoiceType='Sale' and

    c1.CompanyKey >0)

    group by c.CompanyID, d.datedesc

  • March 6, 2009 at 5:14 am

    #954768

    This may be a shot in the dark, because I don't know your data, but...

    Have you tried creating an index using the descending order on the datedesc column?

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

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

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