Optimising Database Calls

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin Eyles - 20 Sep 2006 14:44 GMT

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

Reply to this Message

Richard Brown - 20 Sep 2006 15:08 GMT

> 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
[quoted text clipped - 23 lines]
> 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?

Reply to this Message

Richard Brown - 20 Sep 2006 15:25 GMT

> > 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
[quoted text clipped - 26 lines]
> 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?

Reply to this Message

Martin Eyles - 20 Sep 2006 15:58 GMT

>> > 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
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

Martin Eyles - 20 Sep 2006 15:34 GMT

>> 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
[quoted text clipped - 27 lines]
> 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

Reply to this Message

Martin Eyles - 20 Sep 2006 16:14 GMT

>>> 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
[quoted text clipped - 60 lines]
> 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

Reply to this Message

Jason Kester - 20 Sep 2006 20:23 GMT

> loop 86 times
> construct query
[quoted text clipped - 3 lines]
> 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/

Reply to this Message

bruce barker (sqlwork.com) - 20 Sep 2006 16:30 GMT

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)

>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
[quoted text clipped - 23 lines]
> Thanks,
> Martin

Reply to this Message

Optimising Database Calls

Free Magazines