Board index » delphi » Large result set on SQL Server hangs

Large result set on SQL Server hangs

Hi all
I am using Delphi3.02 C/S and SQL Server.
I have a situation where if I request a large result set (more than 5000
records) from a table and display the results in a DBGrid, and then the
user doesn't do anything for 10 seconds or so, the app hangs when the user
tries to navigate in the grid OR when the query is closed.

An example of the query would be : 'SELECT * FROM Customer'. Not a complex
query, I know<g>, but if I reduce the size of the result set to 1000
records or less, the problem seems to go away.

I have done all the usual to try to fix the problem, I have even created a
simple app that simply opens this query into a DBGrid, with the same
results.

Anyone got any ideas? I have got to the point where I suspect it might be
my BDE configuration, could someone send me a copy of there BDE config for
an SQL Server? Or does anyone have any other suggestions?

As always, any help would be greatly appreciated.

Ta much

John Hair
Designer Systems Ltd

 

Re:Large result set on SQL Server hangs


John Hair <jh...@compuserve.com> wrote in article
<01bd1aac$d29948c0$0f090...@test.action.co.uk>...

Quote
> Hi all
> I am using Delphi3.02 C/S and SQL Server.
> I have a situation where if I request a large result set (more than 5000
> records) from a table and display the results in a DBGrid, and then the
> user doesn't do anything for 10 seconds or so, the app hangs when the
user
> tries to navigate in the grid OR when the query is closed.

John,

Are you sure that the system is hung? If you have more than one query using
a single connection, there will be a delay like you are describing when
re-navigating the first query. For example, if I perform a large query and
then do some other task that uses the shared connection, when I try to use
the large queries results I get the delay.

If you go to the SQL Enterprise Manager and choose Server|Current Activity
off of the menu you can see whether the query is doing anything. Also, you
can see if the query's process is being blocked.

--

Bill Seifert
Software Vision, Inc.

Re:Large result set on SQL Server hangs


John, I have the same problem. The only twist is we get an error message
which states "Access error in SQLSVR32.DLL at Address XXXXXX." If you or
anyone else has a solution it would be greatly appreciated.

Owen Rigdon

Re:Large result set on SQL Server hangs


John

SQL Server does not hang: the behaviour you describe is normal. My
experience is that the BDE does not use the cursors of SQL Server, but
loads the complete set of requested data to somewhere (in memory?, the
server? no idea). You just have to wait until it's loaded. You could try to
use an optimizer hint like "fastfirstrow" or to specify the index name.

Regards

  Wolfgang

John Hair <jh...@compuserve.com> wrote in article
<01bd1aac$d29948c0$0f090...@test.action.co.uk>...

Quote
> Hi all
> I am using Delphi3.02 C/S and SQL Server.
> I have a situation where if I request a large result set (more than 5000
> records) from a table and display the results in a DBGrid, and then the
> user doesn't do anything for 10 seconds or so, the app hangs when the
user
> tries to navigate in the grid OR when the query is closed.

> An example of the query would be : 'SELECT * FROM Customer'. Not a
complex
> query, I know<g>, but if I reduce the size of the result set to 1000
> records or less, the problem seems to go away.

> I have done all the usual to try to fix the problem, I have even created
a
> simple app that simply opens this query into a DBGrid, with the same
> results.

> Anyone got any ideas? I have got to the point where I suspect it might be
> my BDE configuration, could someone send me a copy of there BDE config
for
> an SQL Server? Or does anyone have any other suggestions?

> As always, any help would be greatly appreciated.

> Ta much

> John Hair
> Designer Systems Ltd

Re:Large result set on SQL Server hangs


You might be having a problem with page intent locks... try the query as
follows:

SELECT * FROM Customer(NOLOCK)

When SQL Server returns a large result set, it put page intent locks on it,
causing other operations to appear to hang.

Good luck.

Dave Prothero
Argos Software

Quote
John Hair wrote in message <01bd1aac$d29948c0$0f090...@test.action.co.uk>...
>Hi all
>I am using Delphi3.02 C/S and SQL Server.
>I have a situation where if I request a large result set (more than 5000
>records) from a table and display the results in a DBGrid, and then the
>user doesn't do anything for 10 seconds or so, the app hangs when the user
>tries to navigate in the grid OR when the query is closed.

>An example of the query would be : 'SELECT * FROM Customer'. Not a complex
>query, I know<g>, but if I reduce the size of the result set to 1000
>records or less, the problem seems to go away.

>I have done all the usual to try to fix the problem, I have even created a
>simple app that simply opens this query into a DBGrid, with the same
>results.

>Anyone got any ideas? I have got to the point where I suspect it might be
>my BDE configuration, could someone send me a copy of there BDE config for
>an SQL Server? Or does anyone have any other suggestions?

>As always, any help would be greatly appreciated.

>Ta much

>John Hair
>Designer Systems Ltd

Re:Large result set on SQL Server hangs


Hi all
I tried all of the suggestions made to me, and none have worked.
I had a small brainwave last night and thought I would try something, and
hey presto, it seems to of worked!
After TQuery.Open, I do .Last and .First. I guess that this fixes it (until
next time<g>).
If anyone can explain the reason why this works, I would be very
interested.

Ta much

John Hair
Designer Systems Ltd

Other Threads