|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Urgent: Help on queryI have a large table from which I need to fetch data according to a given filter conditions. But I will only need to fetch a set of records (Eg : To display the first 200 records in the table.). Still I would need the total no. of records which will satisfy the filter to display a text like "Displaying first 200 records out of 50000 records matching the filter criteria" Currently what I do is doing two queries, 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> Running the same query twice would be slow as the number of records would be huge. Can anyone advice me of a way to run an optimize query without running the same query twice. Best Regards, Roshan Jayalath Roshan Jayalath wrote:
Show quote > Hi all, If you are using SQL Server 2005 you can use rowcount function> > I have a large table from which I need to fetch data according to a given > filter conditions. But I will only need to fetch a set of records (Eg : To > display the first 200 records in the table.). Still I would need the total > no. of records which will satisfy the filter to display a text like > "Displaying first 200 records out of 50000 records matching the filter > criteria" > > Currently what I do is doing two queries, > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > Running the same query twice would be slow as the number of records would be > huge. Can anyone advice me of a way to run an optimize query without running > the same query twice. > > Best Regards, > Roshan Jayalath partition on filed which you are filtering. Then you can first 200 row numbers and max row number for each partition in single query. Regards Amish Shah http://shahamishm.tripod.com Hi Amish,
Thanks for the quick response. But unfortunately I have to use SQL Server 2000 for the time beign. Best Regards, Roshan Jayalath Show quote "amish" wrote: > > Roshan Jayalath wrote: > > > Hi all, > > > > I have a large table from which I need to fetch data according to a given > > filter conditions. But I will only need to fetch a set of records (Eg : To > > display the first 200 records in the table.). Still I would need the total > > no. of records which will satisfy the filter to display a text like > > "Displaying first 200 records out of 50000 records matching the filter > > criteria" > > > > Currently what I do is doing two queries, > > > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > > > Running the same query twice would be slow as the number of records would be > > huge. Can anyone advice me of a way to run an optimize query without running > > the same query twice. > > > > Best Regards, > > Roshan Jayalath > > If you are using SQL Server 2005 you can use rowcount function > partition on filed which you are filtering. Then you can first 200 row > numbers and max row number for each partition in single query. > > Regards > Amish Shah > http://shahamishm.tripod.com > > Roshan Jayalath wrote:
Show quote > Hi Amish, Roshan Jayalath,> > Thanks for the quick response. But unfortunately I have to use SQL Server > 2000 for the time beign. > > "amish" wrote: > > > > > Roshan Jayalath wrote: > > > > > Hi all, > > > > > > I have a large table from which I need to fetch data according to a given > > > filter conditions. But I will only need to fetch a set of records (Eg : To > > > display the first 200 records in the table.). Still I would need the total > > > no. of records which will satisfy the filter to display a text like > > > "Displaying first 200 records out of 50000 records matching the filter > > > criteria" > > > > > > Currently what I do is doing two queries, > > > > > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > > > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > > > > > Running the same query twice would be slow as the number of records would be > > > huge. Can anyone advice me of a way to run an optimize query without running > > > the same query twice. > > > > > > Best Regards, > > > Roshan Jayalath > > > > If you are using SQL Server 2005 you can use rowcount function > > partition on filed which you are filtering. Then you can first 200 row > > numbers and max row number for each partition in single query. > > > > Regards > > Amish Shah > > http://shahamishm.tripod.com > > > > I have tried to mimick this on SQL Server 2000 and the best I can come up with is as follows: SELECT TOP 200 A.FullCount, FirstName, LastName FROM fAdrBook, (SELECT COUNT(Int_Key) AS FullCount From fAdrBook WHERE <Filter condition>) A WHERE <Filter condition> However, I doubt whether this helps as it's still two SELECT queries. Regards, -Tav.- Tavis Pitt Roshan,
try saving intermediate results in a temporary table (or table variable): SELECT Int_Key, FirstName, LastName into #t >From fAdrBook WHERE <Filter condition> SELECT TOP 200 FirstName, LastName From #t2. SELECT COUNT(Int_Key) From #t Hi all,
Thanks for all of your kind replies. I tried this one. SELECT TOP 20 A.*, @@ROWCOUNT AS X FROM fAdrBook A, (SELECT B.Int_Key FROM fAdrBook B WHERE <Filter Condition) AS C WHERE A.Int_Key = C.Int_Key But it doesnt work as @@RowCount doesnt give the rowcount returned by the inner select as I expected it to work. Any thoughts ? I also tried something like the below SELECT TOP 20 A.*, B.Cnt FROM fAdrBook A, (SELECT COUNT(B.Int_Key) AS Cnt, B.Int_Key FROM fAdrBook B WHERE <Filter Condition Group By ....) AS C WHERE A.Int_Key = C.Int_Key Int_Key is a unique auto incrementing key. The problem is the group by clause, because if I put B.Int_Key in the select list I ll have to group by it as well, which will cause COUNT(b.Int_Key) to be always 1. Best Regards, Roshan Jayalath Roshan Jayalath wrote:
Show quote > Hi all, Try this> > Thanks for all of your kind replies. I tried this one. > > SELECT TOP 20 A.*, @@ROWCOUNT AS X FROM fAdrBook A, > (SELECT B.Int_Key FROM fAdrBook B WHERE <Filter Condition) > AS C WHERE A.Int_Key = C.Int_Key > > But it doesnt work as @@RowCount doesnt give the rowcount returned by the > inner select as I expected it to work. Any thoughts ? I also tried something > like the below > > SELECT TOP 20 A.*, B.Cnt FROM fAdrBook A, > (SELECT COUNT(B.Int_Key) AS Cnt, B.Int_Key FROM fAdrBook B WHERE <Filter > Condition Group By ....) > AS C WHERE A.Int_Key = C.Int_Key > > Int_Key is a unique auto incrementing key. The problem is the group by > clause, because if I put B.Int_Key in the select list I ll have to group by > it as well, which will cause COUNT(b.Int_Key) to be always 1. > > Best Regards, > Roshan Jayalath SELECT TOP 200 FirstName, LastName, (SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> ) as cnt From fAdrBook WHERE <Filter condition> or SELECT TOP 200 FirstName, LastName, a.cnt From fAdrBook ,(SELECT COUNT(Int_Key) cnt From fAdrBook WHERE <Filter condition> ) a WHERE <Filter condition> Regards Amish Shah http://shahamishm.tripod.com SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> Thanks amish,
But again that will ru the same query more than once aint it ? Roshan Show quote "amish" wrote: > > Roshan Jayalath wrote: > > > Hi all, > > > > Thanks for all of your kind replies. I tried this one. > > > > SELECT TOP 20 A.*, @@ROWCOUNT AS X FROM fAdrBook A, > > (SELECT B.Int_Key FROM fAdrBook B WHERE <Filter Condition) > > AS C WHERE A.Int_Key = C.Int_Key > > > > But it doesnt work as @@RowCount doesnt give the rowcount returned by the > > inner select as I expected it to work. Any thoughts ? I also tried something > > like the below > > > > SELECT TOP 20 A.*, B.Cnt FROM fAdrBook A, > > (SELECT COUNT(B.Int_Key) AS Cnt, B.Int_Key FROM fAdrBook B WHERE <Filter > > Condition Group By ....) > > AS C WHERE A.Int_Key = C.Int_Key > > > > Int_Key is a unique auto incrementing key. The problem is the group by > > clause, because if I put B.Int_Key in the select list I ll have to group by > > it as well, which will cause COUNT(b.Int_Key) to be always 1. > > > > Best Regards, > > Roshan Jayalath > > Try this > > SELECT TOP 200 FirstName, LastName, (SELECT COUNT(Int_Key) From > fAdrBook WHERE <Filter condition> ) as cnt > From fAdrBook WHERE <Filter condition> > > or > > SELECT TOP 200 FirstName, LastName, a.cnt > From fAdrBook ,(SELECT COUNT(Int_Key) cnt From fAdrBook WHERE <Filter > condition> ) a > WHERE <Filter condition> > > > Regards > Amish Shah > http://shahamishm.tripod.com > > SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > Roshan Jayalath wrote:
Show quote > Hi all, How do I page through a recordset?> > I have a large table from which I need to fetch data according to a given > filter conditions. But I will only need to fetch a set of records (Eg : To > display the first 200 records in the table.). Still I would need the total > no. of records which will satisfy the filter to display a text like > "Displaying first 200 records out of 50000 records matching the filter > criteria" > > Currently what I do is doing two queries, > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > Running the same query twice would be slow as the number of records would be > huge. Can anyone advice me of a way to run an optimize query without running > the same query twice. > > Best Regards, > Roshan Jayalath http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html Roshan Jayalath wrote:
Show quote > Hi all, Do you have enough indexes on your tables? Unless "large table" means> > I have a large table from which I need to fetch data according to a given > filter conditions. But I will only need to fetch a set of records (Eg : To > display the first 200 records in the table.). Still I would need the total > no. of records which will satisfy the filter to display a text like > "Displaying first 200 records out of 50000 records matching the filter > criteria" > > Currently what I do is doing two queries, > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > Running the same query twice would be slow as the number of records would be > huge. Can anyone advice me of a way to run an optimize query without running > the same query twice. more than ten million records, there's no reason that running the query through twice should be time consuming. Hi again,
Tracy, Thanks for the resourceful link that you provided. RPresser, Thanks. We do not have more than 10 million records. Maximum it will be five hundred thousand and the required columns are indexed. But still we need to increase the overall performance, to display the data with minimal time. (Eg: For a user to see 10 records in a grid (with the total no. of records matching his filter) it wont be good to take minutes, I guess you would agree. Best Regards, Roshan Show quote "rpresser" wrote: > > Roshan Jayalath wrote: > > Hi all, > > > > I have a large table from which I need to fetch data according to a given > > filter conditions. But I will only need to fetch a set of records (Eg : To > > display the first 200 records in the table.). Still I would need the total > > no. of records which will satisfy the filter to display a text like > > "Displaying first 200 records out of 50000 records matching the filter > > criteria" > > > > Currently what I do is doing two queries, > > > > 1. SELECT TOP 200 FirstName, LastName From fAdrBook WHERE <Filter condition> > > 2. SELECT COUNT(Int_Key) From fAdrBook WHERE <Filter condition> > > > > Running the same query twice would be slow as the number of records would be > > huge. Can anyone advice me of a way to run an optimize query without running > > the same query twice. > > Do you have enough indexes on your tables? Unless "large table" means > more than ten million records, there's no reason that running the query > through twice should be time consuming. > > Roshan Jayalath wrote:
> Hi again, Querying a properly indexed table of a half million records should> > Tracy, Thanks for the resourceful link that you provided. > > RPresser, Thanks. We do not have more than 10 million records. Maximum it > will be five hundred thousand and the required columns are indexed. But still > we need to increase the overall performance, to display the data with minimal > time. (Eg: For a user to see 10 records in a grid (with the total no. of > records matching his filter) it wont be good to take minutes, I guess you > would agree. never take more than a second total for both the count run and returning the first 10 records.  |
|||||||||||||||||||||||
Other interesting topics