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.SqlCo nnection("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.SqlCo mmand(SQL, conn)
Dim r As SqlClient.SqlDa taReader
r = command.Execute Reader
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 7 1373
Martin Eyles wrote:
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.SqlCo nnection("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.SqlCo mmand(SQL, conn)
Dim r As SqlClient.SqlDa taReader
r = command.Execute Reader
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:
Martin Eyles wrote:
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.SqlCo nnection("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.SqlCo mmand(SQL, conn)
Dim r As SqlClient.SqlDa taReader
r = command.Execute Reader
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" <ri************ ***@gmail.comwr ote in message
news:11******** **************@ m7g2000cwm.goog legroups.com...
>
Martin Eyles wrote:
>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 unfortunatle y a bit slow at the moment.
For each of the procedures I go through code of this form
Dim conn As New SqlClient.SqlCo nnection("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.SqlCo mmand(SQL, conn) Dim r As SqlClient.SqlDa taReader r = command.Execute Reader 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" <ri************ ***@gmail.comwr ote in message
news:11******** *************@e 3g2000cwe.googl egroups.com...
>
Richard Brown wrote:
>Martin Eyles wrote:
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" <ma**********@N OSPAMbytronic.c omwrote in message
news:12******** *****@corp.supe rnews.com...
"Richard Brown" <ri************ ***@gmail.comwr ote in message
news:11******** **************@ m7g2000cwm.goog legroups.com...
>> Martin Eyles wrote:
>>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 unfortunatl ey a bit slow at the moment.
For each of the procedures I go through code of this form
Dim conn As New SqlClient.SqlCo nnection("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.SqlCo mmand(SQL, conn) Dim r As SqlClient.SqlDa taReader r = command.Execute Reader 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" <ma**********@N OSPAMbytronic.c omwrote in message
news:12******** *****@corp.supe rnews.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 unfortunatle y a bit slow at the moment.
For each of the procedures I go through code of this form
Dim conn As New SqlClient.SqlCo nnection("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.SqlCo mmand(SQL, conn)
Dim r As SqlClient.SqlDa taReader
r = command.Execute Reader
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:
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jasper Bryant-Greene |
last post by:
I have a database of movie titles, with about 78,000 records, and a
database of related people (directors, writers, actors/actresses etc.)
with about 141,000 records. I display a random movie out of this
database on each hit to my website's homepage.
This worked fine when I had only a couple thousand movies, but now that
the DB has grown, it seems to be taking a bit longer to process the
page.
My DB schema for each table:
|
by: Howie Goodell |
last post by:
Hello --
I am trying to optimise a JDBC connection and an Oracle 9i database
for reading millions of records at a time to a 1 Gig PC running Win2K
or XP. Two questions:
1. Does anyone have suggestions for optimising an Oracle 9i server
(Enterprise Edition, Release 2) for networked read-only JDBC access
with large return sets? With default settings MySQL reads 1M records
2.5 times faster than Oracle even on its first, non-cached...
|
by: Andrew |
last post by:
Hi,
I have a problem I would really appreciate help with. I am generating
dynamic SQL and need to optimise it. The specific example I am trying to
optimise looks like this:
SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE
(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2
WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)
AND...
|
by: Paul Brown |
last post by:
Thanks for the replies Tristan, Eric, Steven & Kurt. They have given
me some good leads.
I present justification for a lot of the comments that drew
(constructive) criticism below. Firstly, let me summarise the feedback
that has pointed me in the right direction :-
>> Steven
> 20.13: What's the best way of making my program efficient?
> A: By picking good algorithms and implementing them carefully.
|
by: Jon Maz |
last post by:
Hi,
I just read the following in an old NG thread:
"when you use the adapter with a closed connection it will open it, do your
requested database access, and close it immediately. If you use the adapter
with an open connection the connection will continue to be open after the
adapter is done."
My DataLayer makes extensive use of DataAdapters to fill DataSets, and
| |
by: BobLaughland |
last post by:
Hi All,
I have a web page that loads data out of a database, and then displays
it on the screen.
There is a lot of logic behind the displaying, some times fields are
made invisible, and sometimes they are hidden.
The page is slow to load, but I must admit that the database query to
load the page is only small, and will only bring back one row, so I am
|
by: Chris Weston |
last post by:
Hi.
Maybe I'm just being dim, but I'm struggling to get my head around
optimising a query with regard to indexes. If I make a select query, such
as a pseudo-example 'select * from bigtable where foo='bar' and
(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', how
could I make that any better? What indexes could I add, or what could I
change in the query?
I know it looks simple, but so am I.
|
by: Eckhart |
last post by:
Dear All, Plz help me in optimising the following query,
Reduce repeatable reads from the table via select ,ythe table sare not
having referntial integrity constarints ,relations
CREATE proc Rolex136Sync
as
DECLARE @date varchar(50),@ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @date =...
|
by: ananth |
last post by:
Hi All,
I have a java code that retrievies values from a table in a oracle database.
There are more than 100 columns and 100000 records for the table in the database and i am able to retrieve 88 records for 5 mins which doesnt suit the requirement.
I need to retrieve 300 records per minute is there any alternative available so that i can optimize the query or use any java methods to retrieve the values in the specified time
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |