Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31

    Unanswered: How can I estimate time, which query needs to complete work?

    As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are far too many factors to estimate the time a query will take to run. I would be surprised if there is a tool and would be even more surprised if its estimates are approaching reliable.

    How big are your databases? They can't be that big if you are using developer edition can they.... Is this on an XP workstation or something?

  3. #3
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Now, biggest table has 4 mln rows, but it will rise to about 50 - 100 mln. Some querys needs a lot of time (for eg. one needs 1h20min for 1,6 mln rows, so with 50 mln rec. it can be usefull to estimate time for some querys ). It's on XP pro, but I'm thinking about Server 2003 (and 4 GB ram) if it'll be usefull. Btw. What is difference between developer and enterprise edition (other than licence)?

  4. #4
    Join Date
    Jan 2008
    Location
    Billings, MT
    Posts
    14
    No difference between Developer and Enterprise. You just can't use Developer in a production environment.

    It's unlikely you'll be able to estimate query execution time. You can look at the execution plan and use DTA to make sure your queries are as efficient as they can be. But beyond that the best you can do is guess that they scale based on table size. So if 1.6M rows take x hours, 50M rows shouldn't take more than 30 times that. But again, that's probably not going to be accurate.
    Tom Rupsis
    Granite Peak Systems
    Phone: 406-672-8292
    Email: trupsis@granitepeaksys.com
    LinkedIn: www.linkedin.com/in/trupsis

  5. #5
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    So, there is some statistics work to do Testing many querys on many random tables, calculate their times to estimating approximate times for really big ones... Thanks for replys!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Ivenesco
    Now, biggest table has 4 mln rows, but it will rise to about 50 - 100 mln. Some querys needs a lot of time (for eg. one needs 1h20min for 1,6 mln rows, so with 50 mln rec. it can be usefull to estimate time for some querys ). It's on XP pro, but I'm thinking about Server 2003 (and 4 GB ram) if it'll be usefull. Btw. What is difference between developer and enterprise edition (other than licence)?
    I'd be interested to know the spec of the machine and either see the code or get an overview of the code. (Not that I've ever tried but I would guess that) Even a cursor would eat up 1.6million rows in less that 1hr 20 mins. Either this is an incredibly slow machine, a poorly coded process, a poorly designed database, or one hell of a complex set of business rules.


    BTW - you can't just scale up the time in multiples (I think tprupsis is saying this too but I'll be explicit). Sometimes you can, it depends. But if you are making good use of your indexes then increases in data volume do not necessarily result in a linear increase in processing time. All "ifs" and "buts" of course.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, since my esp usb port is clogged, you might want to post the sql, and the DDL of the table, including indexes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Machine spec:
    Intel C2D E6400 @ 2x2.8 GHz
    2x1Gb RAM DDR2 (800 MHz) DC
    2x160Gb HDD Seagate in RAID 0
    On Win. XP pro. & Sql Server 2005 Developer edition

    One of the querys (from this forum ):
    INSERT INTO [Queue] ([Link], [MainUrl])
    SELECT b.[Link], b.[MainUrl] FROM [Stack] AS b WHERE
    NOT EXISTS (SELECT c.* FROM [Queue] AS c WHERE c.[Link] = b.[Link])
    Tables Queue and Stack looks like:
    ID -> bigint (primary key, identity, not for replication)
    Link -> nvarchar(800)
    MainUrl -> nvarchar(800)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many rows in the two tables? How come you need to populate queue with everything from stack? Can Link have none ASCI characters? Any index on Link? Does that really take 1 hr 20 mins?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, NOT EXISTS generally hurts.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Also, NOT EXISTS generally hurts.
    I disagree with that one meself, especially in a mega simple query like that. If you can rewrite that query with the same logical meaning but with a better execution plan (I would say different but it is possible to make it worse...) I would be very impressed.

  12. #12
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Quote Originally Posted by pootle flump
    How many rows in the two tables? How come you need to populate queue with everything from stack? Can Link have none ASCI characters? Any index on Link? Does that really take 1 hr 20 mins?
    When testing, I think it was 1,6 mln links in Stack. In Queue about 40k. I don't need everything from Stack: the point is to copy only unique values. Links can have every url which is available in web, only rule is to delete -> ' <-, beceuse mssql doesn't like it Yes, it really takes 1h20min
    Now I'm writing new app. and creating new database to solve this problem. I found new database stucture, which increase speed to acceptable value.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have indexes on the [Link] columns for each table? Because I tried testing your query with 4 million rows and it ran really fast.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    No, it looks exactly what I post before. 4 million in Stack? How many in Queue?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I started with 400,000 in Queue. And mine ran fast even without indexing the Link columns.

    But I would still recommend that you add indexes to the Link columns.

    Also, you don't happen to have any triggers on the Queue table, do you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •