Avatar of JGH5
JGH5
Flag for United States of America asked on

Query Time out

Any way to speed this query up? Cursor mabye? Getting I/O timeouts due to a 400 million record table.


SELECT r.* from Records r (nolock) where $partition.Onefn(ID_Range)=1
and CAST(RIGHT(r.SchID,3) AS INT) BETWEEN 0 AND 15
and date between '1/1/2005' and '1/2008'

Microsoft DevelopmentMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
jorgedeoliveiraborges

8/22/2022 - Mon
Dave Baldwin

Make it a smaller query and see it it returns without a timeout.  According to this page http://msdn.microsoft.com/en-us/library/ms180878%28SQL.100%29.aspx , '1/2008' may not be a valid date format.
jorgedeoliveiraborges

May you send to us the acess path?

SET SHOWPLAN_TEXT ON
go

Open in new window

JGH5

ASKER
that was a typo should have been '1/1/2008'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Marten Rune

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JGH5

ASKER
ShowPlan results minus the object names:

  |--Compute Scalar(DEFINE:
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, OUTER REFERENCES:
OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Filter
                 |    |--Compute Scalar(DEFINE:(
                 |         |--Index Scan(OBJECT:
                 |--Clustered Index Seek(OBJECT:
SEEK: LOOKUP ORDERED FORWARD)
G Trurab Khan

Try like this
select * from
(SELECT r.* from Records r (nolock) where $partition.Onefn(ID_Range)=1) r
where CAST(RIGHT(r.SchID,3) AS INT) BETWEEN 0 AND 15
and date between '1/1/2005' and '1/2008'

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JGH5

ASKER
The query is running now for 25 mins; it times out after an hour.  I am running Ghunaima´s solution; and martenrune was correct with not using the where to access the index.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jorgedeoliveiraborges

Yes, I agree.
Ghunaima´s solution use two levels of nested queries. We can use more. I need more informations to do that.
Wich index filter less rows?
select count (1) from Records r (nolock) where $partition.Onefn(ID_Range)= 1
union all
select count (1) from Records r (nolock) where where x.date between '1/1/2005' and '1/1/2008'

Open in new window

Please send us the result, thanks in advance.
jorgedeoliveiraborges

I would like not to use r.*, and just the strict necessary columns. May I?
r.column1, r.column2, ..., r.column«N». (and not all columns, as in r.*) May we?
JGH5

ASKER
Ghunaima´s solution timed out after 1:56 mins.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jorgedeoliveiraborges

This is the candidate to new anatomy. Please do not send it to the sql engine yet.

May I see the statistics first?

select r3.*
from (
       select r2.*
       from (
              select r1.*
                from (
                       SELECT r.column1, r.column2, r.column3 
                         from Records r (nolock) 
                        where $partition.Onefn (ID_Range) = 1
                      ) r1
            ) r2
       where date between '1/1/2005' and '1/1/2008'
     ) r3
where CAST(RIGHT(r.SchID,3) AS INT) BETWEEN 0 AND 15

Open in new window

jorgedeoliveiraborges

select r2.*
from (
       select r1.*
         from (
                SELECT r.column1, r.column2, r.column3,
                       r.SchID
                  from Records r (nolock) 
                 where $partition.Onefn (ID_Range) = 1
                   and r.date between '1/1/2005' and '1/1/2008'
               ) r1
     ) r2
where CAST(RIGHT(r2.SchID,3) AS INT) BETWEEN 0 AND 15;

Open in new window

Anthony Perkins

You can optimize this query to death, but the bottom line is that you are returning too many rows.  In other words the problem is not with SQL Server, but rather your network.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jorgedeoliveiraborges

line 2: into  TheSameDatabase.Results

select r2.*
into  TheSameDatabase.Results
from (
       select r1.*
         from (
                SELECT r.column1, r.column2, r.column3,
                       r.SchID
                  from Records r (nolock) 
                 where $partition.Onefn (ID_Range) = 1
                   and r.date between '1/1/2005' and '1/1/2008'
               ) r1
     ) r2
where CAST(RIGHT(r2.SchID,3) AS INT) BETWEEN 0 AND 15;

Open in new window