Filter - recordcount - speed problem

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

Filter - recordcount - speed problem

Author

Topic

sridards
Starting Member

6 Posts

Posted - 2010-11-08 : 06:00:40

Hi,

I have a table with records of 150000. when i use filter in table component, opening the table and giving message with table.recordcount to check number of records. It takes almost 1 minute to give message with number of records.

Can anybody give me reason for this also how to avoid this much slow? I have checked both with / without index in the table compoent but the result is same. kindly reply.

Thanks
S.Sridhar

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 06:10:19

So what is your table structure and the query?

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-08 : 06:10:19

Can you please post your query?

Vaibhav T

If I cant go back, I want to go fast...

sridards
Starting Member

6 Posts

Posted - 2010-11-09 : 01:22:30

It is not query, i am using program in delphi which has table component to attach database and table from sqlserver. Database is connected through ODBC-->Sqlserver. i am attaching database and table in table component and giving filter condition in one of the properties in table component and opening the table component. it is opening very fast, but while checking number of records with command in delphi as "table1.recordcount", it takes minimum 1 minute.

This table contains 144 fields with 1 one primary key index and 5 non-clustered indexes. 2,50,000 records.

Thanks
S.Sridhar

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-09 : 03:24:29

eductation lesson: everything that reads a database in some way generates a query....even if you don't program it yourself.

create a SP as
select count(*) from yourtable

then call the SP from your code.

part of the problem you are having could be caused by you transferring the entire data from the server down to your client and counting it locally record by record. this may be caused by the type of recordset yuo have defined in your application for this query.

but since you haven't posted your code....we can can only guess.
help us, help you.

post something that would be useful to a stranger.

sridards
Starting Member

6 Posts

Posted - 2010-11-10 : 02:33:25

i know query is generated internally even if it is a table component used, thats why i have specified number of records and number of fields in the table. so it means the query is select * from <table name> where <condition>.

Also i have specified i don't have problem with fetching the data. i can see immediately all the records in my data grid, there is no problem / delay in transfering of data from server to client.

My only problem is, i need to display number of records in a message box, so when i write the code as table1.recordcount ( in delphi, this command returns number of rows in the table component with table and filter condition) it takes several seconds to display the message.

I hope you can understand it better now.

if you want you can see the following program:

Table1 : TTable;

Table1.databasename := <Database name>
table1.tablename := <Tablename>
table1.filter := <field=value>
table1.filtered := True;
table1.open;(to open the table) (here no time delay, i can attach a datasource and see in the grid)

Showmessage(table1.recordcount) (to give message with number of records that are fetched) - here i have a problem in time delay.

Thanks
S.Sridhar

Kristen
Test

22859 Posts

Posted - 2010-11-10 : 02:53:01

AndrewMurphy is saying that perhaps

table1.recordcount

causes this query to be sent to the database:

SELECT * FROM MyTable WHERE ...

which will pull ALL the data locally, and then count it.

Whereas what you want it to do is

SELECT COUNT(*) FROM MyTable WHERE ...

So ... assuming that Delphi is sending the inefficient query, then AndrewMurphy's suggestion is that YOU do the

SELECT COUNT(*) FROM MyTable WHERE ...

query (rather than relying on Delphi's inbuilt table1.recordcount method) and display the result in place of your "Showmessage(table1.recordcount)"

I think: ODBC has a RecordCount property on a recordset. This is ONLY valid (AFAIK) when all the rows have been retrieved. Maybe your GRID is only pulling the first few rows, so it appears quickly, but when you ask for table1.recordcount the effect is that ODBC is told to "retrieve all rows" before the RecordCount property is used - which is very different from just doing a SELECT COUNT(*) FROM MyTable WHERE ... I'm afraid!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-10 : 06:14:20

Trust a true native of the English language to surpass my weak prose!
Thanks for shining much needed light on my limitied suggestion.

Kristen
Test

22859 Posts

Posted - 2010-11-10 : 18:20:03

Nothing to compare with the Irish lilt though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources