incomplete returns on simple queries to large tables - help!

incomplete returns on simple queries to large tables - help!

Post by Jason K McCan » Tue, 29 Sep 1998 04:00:00



In SQL 6.5 I've got some tables with almost 3 million records.  Horizontally
they arent very big - about 14 columns, the largest being 60 chars (most are
around 10 chars).  If I run a query to simply return 10000 records from
somewhere in the middle of the table like:
    select * from tablez where counter between 1000000 and 1010000

I get this pause in the return of the last few records that is causing me
all kinds of headaches.  (Counter is a primary key, clustered with a fill of
99%)
I get a snappy response on the first, say, 9972 records (it's always
different), then it just stops as though the remaining data is caught up in
a buffer somewhere along the line.  With ISQL/w this is annoying but not
fatal to the query (ISQL is very patient).  With Access, however, it
is often fatal to the query.  If I try to go to the records that havent been
returned yet, an ODBC error message is generated and the whole return table
is lost (?!NAME?!)
    One interesting thing is that I can slowly coax the rest of the data out
by running the same query in another instance of ISQL/w, or if I wait long
enough it arrives on its own time (a couple of mins, at least).
    How can I fix this?  TIA!
Jason

 
 
 

incomplete returns on simple queries to large tables - help!

Post by Greg Druia » Tue, 29 Sep 1998 04:00:00


With Access you can set the ODBC Timeout property -- use 0 for no timeout (the
query will run till it finishes) or choose a number representing the number of
seconds you want let the query run till it times out.

> In SQL 6.5 I've got some tables with almost 3 million records.  Horizontally
> they arent very big - about 14 columns, the largest being 60 chars (most are
> around 10 chars).  If I run a query to simply return 10000 records from
> somewhere in the middle of the table like:
>     select * from tablez where counter between 1000000 and 1010000

> I get this pause in the return of the last few records that is causing me
> all kinds of headaches.  (Counter is a primary key, clustered with a fill of
> 99%)
> I get a snappy response on the first, say, 9972 records (it's always
> different), then it just stops as though the remaining data is caught up in
> a buffer somewhere along the line.  With ISQL/w this is annoying but not
> fatal to the query (ISQL is very patient).  With Access, however, it
> is often fatal to the query.  If I try to go to the records that havent been
> returned yet, an ODBC error message is generated and the whole return table
> is lost (?!NAME?!)
>     One interesting thing is that I can slowly coax the rest of the data out
> by running the same query in another instance of ISQL/w, or if I wait long
> enough it arrives on its own time (a couple of mins, at least).
>     How can I fix this?  TIA!
> Jason


 
 
 

incomplete returns on simple queries to large tables - help!

Post by Roy Harv » Tue, 29 Sep 1998 04:00:00


Jason,

Apparently SQL Server is not using any index for the test.  This means
that it must scan the entire 3 million row table.  I would further bet
that the rows "here counter between 1000000 and 1010000" are near the
front of the table.  The scan finds these first, returns them (except
perhaps the last buffer full) but then must read through the rest of
the table to finish the query.

Now the question is WHY isn't it using the clustered index on counter?
I don't think you mention the data type of counter, but I will guess
that it is DECIMAL (or NUMERIC, which is the same thing.)  There is a
bug with the optimizer ignoring an index on a DECIMAL column if the
literal numbers do not have a decimal point.  If counter is DECIMAL or
NUMERIC try changing the query to:

  select * from tablez where counter between 1000000. and 1010000.

Roy


Quote:>In SQL 6.5 I've got some tables with almost 3 million records.  Horizontally
>they arent very big - about 14 columns, the largest being 60 chars (most are
>around 10 chars).  If I run a query to simply return 10000 records from
>somewhere in the middle of the table like:
>    select * from tablez where counter between 1000000 and 1010000

>I get this pause in the return of the last few records that is causing me
>all kinds of headaches.  (Counter is a primary key, clustered with a fill of
>99%)
>I get a snappy response on the first, say, 9972 records (it's always
>different), then it just stops as though the remaining data is caught up in
>a buffer somewhere along the line.  With ISQL/w this is annoying but not
>fatal to the query (ISQL is very patient).  With Access, however, it
>is often fatal to the query.  If I try to go to the records that havent been
>returned yet, an ODBC error message is generated and the whole return table
>is lost (?!NAME?!)
>    One interesting thing is that I can slowly coax the rest of the data out
>by running the same query in another instance of ISQL/w, or if I wait long
>enough it arrives on its own time (a couple of mins, at least).
>    How can I fix this?  TIA!
>Jason

 
 
 

incomplete returns on simple queries to large tables - help!

Post by Jason K McCan » Fri, 02 Oct 1998 04:00:00


    How do I get Access to append this decimal point to a query it sends to
the sql server?  If I have a linked table in Access and update one of the
fields, Access generates a simple update query based on the counter field
(i.e. update tablez set state="NY" where counter = "100001"), but since it
doesnt use the index it takes 29 seconds just to update one field!!  Can I
get Access to use a decimal point in these queries?  TIA!
Jason
 
 
 

incomplete returns on simple queries to large tables - help!

Post by Francis Stanisc » Sat, 03 Oct 1998 04:00:00


I would go with the latter suggestion. Setting the timeout to 0 does precisely
what Greg states, keeps running
till it's done, now just imagine the impact on your server if you have all your
users running a query indefinitely.

You don't want that, trust me.

Roy made an interesting point, try using Stats, you may confirm what he was
saying.

Francis


> With Access you can set the ODBC Timeout property -- use 0 for no timeout (the
> query will run till it finishes) or choose a number representing the number of
> seconds you want let the query run till it times out.


> > In SQL 6.5 I've got some tables with almost 3 million records.  Horizontally
> > they arent very big - about 14 columns, the largest being 60 chars (most are
> > around 10 chars).  If I run a query to simply return 10000 records from
> > somewhere in the middle of the table like:
> >     select * from tablez where counter between 1000000 and 1010000

> > I get this pause in the return of the last few records that is causing me
> > all kinds of headaches.  (Counter is a primary key, clustered with a fill of
> > 99%)
> > I get a snappy response on the first, say, 9972 records (it's always
> > different), then it just stops as though the remaining data is caught up in
> > a buffer somewhere along the line.  With ISQL/w this is annoying but not
> > fatal to the query (ISQL is very patient).  With Access, however, it
> > is often fatal to the query.  If I try to go to the records that havent been
> > returned yet, an ODBC error message is generated and the whole return table
> > is lost (?!NAME?!)
> >     One interesting thing is that I can slowly coax the rest of the data out
> > by running the same query in another instance of ISQL/w, or if I wait long
> > enough it arrives on its own time (a couple of mins, at least).
> >     How can I fix this?  TIA!
> > Jason

 
 
 

1. incomplete returns on simple queries to a large table

In SQL 6.5 I've got some tables with almost 3 million records.
Horizontally they arent very big - about 14 columns, the largest being
60 chars (most are around 10 chars).  If I run a query to simply return
10000 records from somewhere in the middle of the table like:
    select * from tablez where counter between 1000000 and 1010000

I get this pause in the return of the last few records that is causing
me all kinds of headaches.  (Counter is a primary key, clustered with a
fill of 99%)
I get a snappy response on the first, say, 9972 records (it's always
different), then it just stops as though the remaining data is caught up
in a buffer somewhere along the line.  With ISQL/w this is annoying but
not fatal to the query (ISQL is very patient).  With Access, however, it
is often fatal to the query.  If I try to go to the records that havent
been returned yet, an ODBC error message is generated and the whole
return table is lost (?!NAME?!)
    One interesting thing is that I can slowly coax the rest of the data
out by running the same query in another instance of ISQL/w, or if I
wait long enough it arrives on its own time (a couple of mins, at
least).
    How can I fix this?  TIA!
Jason

2. mass import to table with unique index

3. incomplete returns on simple queries to a large table - help!

4. Book ...

5. select query returning incomplete record set

6. interesting table matrix problem

7. ODBC returns incomplete rows for a select query

8. querying and cursors on large databases returning large resultsets

9. Help - Simple SQL query doing full table scans

10. Help with Query on large Table

11. SIMPLE PROBLEM: Used SetupWizard, but final app is incomplete