Optimising Database Calls

I have an ASP page that makes a vml/svg graph out of data. In order to
extract all the necessary data I have to perform 68 SQL queries, which is
unfortunatley a bit slow at the moment.

For each of the procedures I go through code of this form

Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
User ID=***; Password=***")
conn.Open()
SQL = "" 'in operation has code to dynamically construct one of the
queries.
Dim command As New SqlClient.SqlCommand(SQL, conn)
Dim r As SqlClient.SqlDataReader
r = command.ExecuteReader
While r.Read
'Code here to extract data and plot one section of the graph
End While
r.Close()
conn.Close()

Is there anything I am doing here that would make the code slower. For
instance, can I share one connection, and with this speed things up? Is
there a way to keep the same command object and keep changing the SQL? would
this be any faster? Any other ideas on how to speed this up?

Thanks,
MartinMartin Eyles wrote:

Quote:

Originally Posted by

I have an ASP page that makes a vml/svg graph out of data. In order to
extract all the necessary data I have to perform 68 SQL queries, which is
unfortunatley a bit slow at the moment.
>
For each of the procedures I go through code of this form
>
Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
User ID=***; Password=***")
conn.Open()
SQL = "" 'in operation has code to dynamically construct one of the
queries.
Dim command As New SqlClient.SqlCommand(SQL, conn)
Dim r As SqlClient.SqlDataReader
r = command.ExecuteReader
While r.Read
'Code here to extract data and plot one section of the graph
End While
r.Close()
conn.Close()
>
Is there anything I am doing here that would make the code slower. For
instance, can I share one connection, and with this speed things up? Is
there a way to keep the same command object and keep changing the SQL? would
this be any faster? Any other ideas on how to speed this up?
>
Thanks,
Martin

What are you actualy tring to do? Why not try and shift some of the
work to the SQL server and reduce the number of round trips?
Richard Brown wrote:

Quote:

Originally Posted by

Martin Eyles wrote:
>

Quote:

Originally Posted by

I have an ASP page that makes a vml/svg graph out of data. In order to
extract all the necessary data I have to perform 68 SQL queries, which is
unfortunatley a bit slow at the moment.

For each of the procedures I go through code of this form

Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
User ID=***; Password=***")
conn.Open()
SQL = "" 'in operation has code to dynamically construct one of the
queries.
Dim command As New SqlClient.SqlCommand(SQL, conn)
Dim r As SqlClient.SqlDataReader
r = command.ExecuteReader
While r.Read
'Code here to extract data and plot one section of the graph
End While
r.Close()
conn.Close()

Is there anything I am doing here that would make the code slower. For
instance, can I share one connection, and with this speed things up? Is
there a way to keep the same command object and keep changing the SQL? would
this be any faster? Any other ideas on how to speed this up?

Thanks,
Martin

>
What are you actualy tring to do? Why not try and shift some of the
work to the SQL server and reduce the number of round trips?

I don't pretend to know much more about SVG than what I've just googled
for but if it is as described on w3.org "a language for describing
two-dimensional graphics in XML" then can you not find a way to pass in
all necessary variables to your stored from and have it return the xml
necessary?
"Richard Brown" <richard.ian.brown@.gmail.comwrote in message
news:1158761336.431608.299510@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

>
Martin Eyles wrote:
>

Quote:

Originally Posted by

>I have an ASP page that makes a vml/svg graph out of data. In order to
>extract all the necessary data I have to perform 68 SQL queries, which is
>unfortunatley a bit slow at the moment.
>>
>For each of the procedures I go through code of this form
>>
>Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
>User ID=***; Password=***")
>conn.Open()
> SQL = "" 'in operation has code to dynamically construct one of the
>queries.
> Dim command As New SqlClient.SqlCommand(SQL, conn)
> Dim r As SqlClient.SqlDataReader
> r = command.ExecuteReader
> While r.Read
> 'Code here to extract data and plot one section of the graph
> End While
> r.Close()
>conn.Close()
>>
>Is there anything I am doing here that would make the code slower. For
>instance, can I share one connection, and with this speed things up? Is
>there a way to keep the same command object and keep changing the SQL?
>would
>this be any faster? Any other ideas on how to speed this up?
>>
>Thanks,
> Martin

>
What are you actualy tring to do? Why not try and shift some of the
work to the SQL server and reduce the number of round trips?
>

The reason I have 86 queries is because I am plotting 86 lines (some filled,
some not) to a graph. The problem is, because of the way this data is stored
on the server, it's not really possible to return a nice rectangular array.
Is there a way I could put lots of queries into one command object, much as
you can run several queries at once in Query Analyzer, and then cycle round
the data sets as an outer loop, cycling round the actual data as an inner
loop.

ie. go from this

loop 86 times
construct query
perform query
for each data point
plot data
next
end

to this

loop 86 times
construct query
end

perform all queries

loop 86 time
for each data point
plot data
next
end
"Richard Brown" <richard.ian.brown@.gmail.comwrote in message
news:1158762356.215592.98810@.e3g2000cwe.googlegrou ps.com...

Quote:

Originally Posted by

>
Richard Brown wrote:

Quote:

Originally Posted by

>Martin Eyles wrote:
>>

Quote:

Originally Posted by

I have an ASP page that makes a vml/svg graph out of data. In order to
extract all the necessary data I have to perform 68 SQL queries, which
is
unfortunatley a bit slow at the moment.

>
I don't pretend to know much more about SVG than what I've just googled
for but if it is as described on w3.org "a language for describing
two-dimensional graphics in XML" then can you not find a way to pass in
all necessary variables to your stored from and have it return the xml
necessary?
>

For the purposes of this graph, the begining and end of each output SVG/VML
code line (which also happens to represent one of the 86 graph lines) can be
thought of as predefined arbitrary text. (If you're interested, I used an
element that is valid in both VML and SVG, and added the headers needed for
both, so that the graph shows in both IE and firefox. However this is not
the likely cause of the slowdown, so it is the next bit that is more
important).

The data I am extracting, which goes between these, can be thought of as a
comma seperated list of x and y coordinates (ie. x1, y1, x2, y2, x3, y3)
where duplicates of y coordinates are not added.
"Martin Eyles" <martin.eyles@.NOSPAMbytronic.comwrote in message
news:12h2kckflder132@.corp.supernews.com...

Quote:

Originally Posted by

"Richard Brown" <richard.ian.brown@.gmail.comwrote in message
news:1158761336.431608.299510@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

>>
>Martin Eyles wrote:
>>

Quote:

Originally Posted by

>>I have an ASP page that makes a vml/svg graph out of data. In order to
>>extract all the necessary data I have to perform 68 SQL queries, which
>>is
>>unfortunatley a bit slow at the moment.
>>>
>>For each of the procedures I go through code of this form
>>>
>>Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
>>User ID=***; Password=***")
>>conn.Open()
>> SQL = "" 'in operation has code to dynamically construct one of the
>>queries.
>> Dim command As New SqlClient.SqlCommand(SQL, conn)
>> Dim r As SqlClient.SqlDataReader
>> r = command.ExecuteReader
>> While r.Read
>> 'Code here to extract data and plot one section of the graph
>> End While
>> r.Close()
>>conn.Close()
>>>
>>Is there anything I am doing here that would make the code slower. For
>>instance, can I share one connection, and with this speed things up? Is
>>there a way to keep the same command object and keep changing the SQL?
>>would
>>this be any faster? Any other ideas on how to speed this up?
>>>
>>Thanks,
>> Martin

>>
>What are you actualy tring to do? Why not try and shift some of the
>work to the SQL server and reduce the number of round trips?
>>

>
The reason I have 86 queries is because I am plotting 86 lines (some
filled, some not) to a graph. The problem is, because of the way this data
is stored on the server, it's not really possible to return a nice
rectangular array. Is there a way I could put lots of queries into one
command object, much as you can run several queries at once in Query
Analyzer, and then cycle round the data sets as an outer loop, cycling
round the actual data as an inner loop.
>
ie. go from this
>
loop 86 times
construct query
perform query
for each data point
plot data
next
end
>
to this
>
loop 86 times
construct query
end
>
perform all queries
>
loop 86 time
for each data point
plot data
next
end

By the way, although I understand this structure, I don't know how to select
out the result from one of the 86 queries in this using VB.net, so I would
still appreciate help in getting that bit sorted out.

Thanks,
Martin
you should convert this to one query batch with a row/resultset coming back
for each line. put all the queries into one sqlcommand.

-- bruce (sqlwork.com)

"Martin Eyles" <martin.eyles@.NOSPAMbytronic.comwrote in message
news:12h2hd43cuof4db@.corp.supernews.com...

Quote:

Originally Posted by

>I have an ASP page that makes a vml/svg graph out of data. In order to
>extract all the necessary data I have to perform 68 SQL queries, which is
>unfortunatley a bit slow at the moment.
>
For each of the procedures I go through code of this form
>
Dim conn As New SqlClient.SqlConnection("Data Source=" & ServerName & ";
User ID=***; Password=***")
conn.Open()
SQL = "" 'in operation has code to dynamically construct one of the
queries.
Dim command As New SqlClient.SqlCommand(SQL, conn)
Dim r As SqlClient.SqlDataReader
r = command.ExecuteReader
While r.Read
'Code here to extract data and plot one section of the graph
End While
r.Close()
conn.Close()
>
Is there anything I am doing here that would make the code slower. For
instance, can I share one connection, and with this speed things up? Is
there a way to keep the same command object and keep changing the SQL?
would this be any faster? Any other ideas on how to speed this up?
>
Thanks,
Martin
>

Martin Eyles wrote:

Quote:

Originally Posted by

loop 86 times
construct query
perform query
for each data point
plot data
next
end

This is actually not as inefficient as you might think. ADO.NET will
keep your connection around for the whole request, so you won't
actually be reconnecting to the database 86 times in a row.

Still, I'd try to construct a query that will hand back all the data
you need to plot every point. You haven't really given a reason why
you couldn't do this, and for something as simple as asking the DB for
points on a bunch of lines, it sounds like a pretty simple query.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/