interesting performance issue getting large data (1st time vs 2+ times) from sql server

  • Thread starter Thread starter Aussie Rules
  • Start date Start date
A

Aussie Rules

Hi
I have a program which gets around 30,000 records from a sql table.

When I do this for the first time in my application, it takes around 15
seconds for the dataset to be populated, however at any other time after the
first, it takes a split second to do it.

I assume this is some sort of caching going on, and I want to find a way to
do it to my advantage.

The problem is that when the user first views the data, the time(15+
seconds) is way to long. I was thinking that if its a caching issue, then I
could perhaps cache the data in the program start up. Problem is the program
use to start pretty quick, and now its taking at least the 15 seconds it
takes to get the data, which makes sense of course.

So then I thought I could do it in a thread so that the startup is not
slowed down, but that seems not to be working as I expected.

Is there any other trick(s) I could try. Its not a connection pool, as the
applicaton opens the connection up front with other commands, before doing
the big data query.

Thanks
 
Could it be that data are cached server side ? For example what if you run
this query twice using SQL Server Management Studio ?

Generally you try to filter as early as possible to avoid returning
thousands of records to the user. How the user will find the row(s) he is
interested within all those rows ?
 
Aussie Rules said:
Hi
I have a program which gets around 30,000 records from a sql table.

When I do this for the first time in my application, it takes around 15
seconds for the dataset to be populated, however at any other time after
the first, it takes a split second to do it.

I assume this is some sort of caching going on, and I want to find a way
to do it to my advantage.

The problem is that when the user first views the data, the time(15+
seconds) is way to long. I was thinking that if its a caching issue, then
I could perhaps cache the data in the program start up. Problem is the
program use to start pretty quick, and now its taking at least the 15
seconds it takes to get the data, which makes sense of course.

So then I thought I could do it in a thread so that the startup is not
slowed down, but that seems not to be working as I expected.

Is there any other trick(s) I could try. Its not a connection pool, as the
applicaton opens the connection up front with other commands, before doing
the big data query.

Thanks
Click to expand...
Sql Server caches the query when you call it, so it will always be much
faster the second time. I would suggest you look at the indexing of your
tables to achieve better performance. Sql Server provides some good tools
for this.

Your idea of using a background worker, on the other hand, should get the
data on a separate thread and not block your application. I generally use a
component class for this type of work. The class can return your data to the
application when it has finished its work, without blocking your
application.

What do you mean when you say your thread is "not working as expected"?
 
Hi

I did the query in SQL query and it performed the same, so looking at the
query I added a clustered index, and am now having to force the query to
default to include a where clause on the clustered index table. Not ideal,
but should be fine as it is now pulling less data, but fast.

Thanks
 
Back
Top