timeout expired - 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

General SQL Server Forums

New to SQL Server Programming

timeout expired

Author

Topic

anitha2324
Starting Member

18 Posts

Posted - 2008-10-03 : 15:12:06

Hi All,

I am using sql server 2000 , I was executing the following query successfully from the enterprise manager when the no of the records were around 60000000 records , but after increasing the records in the table to more then 800000000 i am getting the following error "Microsoft ODBC server error timeout expired" ,

the query is


SELECT     REPLACE(REPLACE(REPLACE(REPLACE(LEFT(mob, 5), '98941', 'Operator1'), '98942', 'Operator2'), '98943', 'Operator3'), '98944', 'Operator4') 
                      AS gsmOp, LEFT(jdate, 11) AS dt, COUNT(*) AS cnt, DAY(jdate) AS dy, MONTH(jdate) AS ASmnth
FROM         allMsgs
GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(LEFT(mob, 5), '98941', 'Operator1'), '98942', 'Operator2'), '98943', 'Operator3'), '98944', 'Operator4'), 
                      LEFT(jdate, 11), DAY(jdate), MONTH(jdate)
ORDER BY MONTH(jdate), DAY(jdate)

I have a primary key called mId which is not used in the above query ,

I have made indexes on jdate and mob columns but the problem still exists,

I have change the default query time out from 600 to 0 , but am still getting the same error , is it necessary to restart sql server service , or is there any way to execute such commands

by the way i don't want to create a procedure for this query because i have so many similar queries and so i have to change all of those queries

so is there any settings in the sql server to change , plz help me am newbie to sql server

Anitha

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-03 : 15:51:22

couple things:
1. don't use Enterprise Manager to run queries, use Query Analyzer.
2. Do you really need to query all 800million rows every query? Can't you include a WHERE clause to limit the output to just the time period you are interested in?
3. Once you CONVERT an indexed column to another datatype via MONTH() sql can't use that index.

We may be able to optimize that query some but 800million aggregated rows is still a lot to expect.

Be One with the Optimizer
TG

anitha2324
Starting Member

18 Posts

Posted - 2008-10-03 : 16:08:41

am sorry they are not 800 million records , they were 600000 ( six hundred thousand ) and they have increased to 800000( eight hunderd thousand)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-03 : 16:44:12

That is a lot better but the points (above) still apply?
What do the [mob] values look like? Post some examples.

try this in a QA window (not enterprise manager)


select gsmOp
       ,dt
       ,cnt
       ,day(dt) dy
       ,month(dt) ASmnth
from   (
       select case 
                     when mob like '98941%' then 'Operator1'
                     when mob like '98942%' then 'Operator2'
                     when mob like '98943%' then 'Operator3'
                     when mob like '98944%' then 'Operator4'
                     else left(mob, 5)
                     end as gsmOp
              ,dateadd(day, datediff(day, 0, jdate), 0) dt
              ,count(*) cnt
       from   allMsgs
       group by case 
                     when mob like '98941%' then 'Operator1'
                     when mob like '98942%' then 'Operator2'
                     when mob like '98943%' then 'Operator3'
                     when mob like '98944%' then 'Operator4'
                     else left(mob, 5)
                     end
              ,dateadd(day, datediff(day, 0, jdate), 0)
       ) d
order by dt

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources