Slow Database data retrieval

  • Thread starter Thread starter Zahid
  • Start date Start date
Z

Zahid

Hi,

My application retrieves a max of 20 rows of data (40
columns per row) each time a SQL Query is executed and as
a result refreshed the text and color on buttons in the
application. For some strange reason it takes 2-3 secs to
execute the query and return the data (20 rows).

What is actually happening is that I am executing 3 small
queries to obtain 3 seperate values and I believe this is
where the delay is happening. Here is the code:

If gListMenu = 0 Then
'Dont bother searching
Else
'Search for listMenuLink
gMyCommand.CommandText = "Select link from
listHdrs where listNo = " & gListMenu
gRdr = gMyCommand.ExecuteReader
(CommandBehavior.SingleRow)

If gRdr.Read() Then
gListMenuLink = gRdr.GetInt32(0)
gRdr.Close()
Else
gRdr.Close()
End If
End If

If gListQual = 0 Then
'Dont bother searching
Else
'Search for listMenuLink
gMyCommand.CommandText = "Select link from
listHdrs where listNo = " & gListQual
gRdr = gMyCommand.ExecuteReader
(CommandBehavior.SingleRow)

If gRdr.Read() Then
gListQualLink = gRdr.GetInt32(0)
gRdr.Close()
Else
gRdr.Close()
End If
End If

If gListExtra = 0 Then
'Dont bother searching
Else
'Search for listExtraLink
gMyCommand.CommandText = "Select link from
listHdrs where listNo = " & gListExtra
gRdr = gMyCommand.ExecuteReader
(CommandBehavior.SingleRow)

If gRdr.Read() Then
gListExtraLink = gRdr.GetInt32(0)
gRdr.Close()
Else
gRdr.Close()
End If

End If

This is an unacceptable speed - im sure it can be much
faster - like 1 second execution time?

Any ideas on how to improve the speed? I would use batch
queries but its not supported on SQLServerCE.

Thanks in advance.
 
Hi Zahid,

Firstly, have you tried indexing on the fields?

Secondly, since I'm a c# guy, I would do something like this....


gMyCommand.CommandText = "select link from listhdrs where listNo = "+
gListMenu.ToString();
object o = gMyCommand.ExecuteScalar();

if (o != null)
{
gListMenuLink = Convert.ToInt32(gResult);
}



.... I'm sure you can work that out. The ExecuteScalar method is geared to
return a single/first value from a single/first result without the need for
a datareader. I would guess that it would be quicker.


Cheers,

Chris
 
Another thought, change your logic so that you execute a single query such
as...

"select link, listno from listhdrs where listno in (" & gListMenu & "," &
gListQual& "," & gListExtra & ")"

create the datareader as you already do....

then loop thru the results, checking the value of 'listno'....(in c#...)

while (dr.read())
{
int listno = gRdr.GetInt32(1);

if (listno == gListMenu)
{
// do processing here
}
else
if (listno = gListQual)
{

// do processing here
}
else
if (listno = gListExtra)
{

// do processing here
}
}


.... you could use a 'switch' statement instead of the if..else block. You
may need to fine-tune it if you expect that any of the gList.... fields
could be the same value, just remove the 'else' clauses from he 'if' block
should do it.

Cheers


Chris
 
if the data is all in one table, why do you need 3
queries?
What I do for speed is fill my datagrids and/or listviews
by running a datareader. it acts like a fast forward read
nly cursor.
 
Hi,

the primary key on the listhdrs table is ListNo - which
is why im querying using it. I thought Indexing was the
same as using primary keys? No....woops....how do you
index then?

Are you saying your method is faster than the method im
using?

Thanks in advance.
 
I'm saying, that in my experience with sqlserverce, you'll need to
experiment a bit. I would _expect_ my method to be faster on the face of it,
but you'll have to try it to see.

Chris
 
Hi,

the primary key on the listhdrs table is ListNo - which
is why im querying using it. I thought Indexing was the
same as using primary keys? No....woops....how do you
index then?
Click to expand...

"Create Index On "+YourTableName+" ("+YourFieldName+")"

And as far as i could see you are querying 3 times from the same table with
different Key.

What about:

Select YourFields From YourTable
Where YourIndexField = YourValue1
or YourIndexField = YourValue2
or YourIndexField = YourValue3

this should be faster than using 3 Queries. Because the Database has to
parse and optimize the Query-Statement just once - not 3 times.

You also could try to use a parameterized Query like this:

"Select YourFields From YourTable Where YourIndexField = ?"
And then "Prepare" the Query.

After that you can use the same query many times and just fill in the
parameter (Parameter[0] = YourValue) and execute the query.

I'm not familiar with the correct syntax of this in C# with SQL-CE but i
know it's documented because i have used it once

Boris
 
Back
Top