Optimising Database Calls

[Martin Eyles]

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

 

[Richard Brown]

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]

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?

 

[Martin Eyles]

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

 

[Martin Eyles]

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]

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

 

[bruce barker \(sqlwork.com\)]

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)

 

[Jason Kester]

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/