Hi,
how would I go about sending a Update Script (with multiples Batches) via
ADO to an SQL 2000 Server.
As much as I know the Command.CommandText can only take a single query.
Thanks a lot
PatrickHi Patrick
You can try using UpdateBatch option available in ADO
thanks and regards
Chandra
"Patrick Wolf" wrote:
> Hi,
> how would I go about sending a Update Script (with multiples Batches) via
> ADO to an SQL 2000 Server.
> As much as I know the Command.CommandText can only take a single query.
> Thanks a lot
> Patrick
>
>|||Hi Chandra,
thanks but this is to batch update a recordset. Eg. if you make multiple
updates in a recordset it enables you to post them all at once.
It doesnt allow you to pass in a script.
Thanks
Patrick
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:F00BB8E6-3D0F-46E1-9111-95F68AE3CF88@.microsoft.com...
> Hi Patrick
> You can try using UpdateBatch option available in ADO
> thanks and regards
> Chandra
>
> "Patrick Wolf" wrote:
>|||Hi Patrick
You can try executing the different scripts in a loop or u can send multiple
queries saperated by a semi colon.
Command.CommandText "query1;query2;query3"
thanks and regards
Chandra
"Patrick Wolf" wrote:
> Hi Chandra,
> thanks but this is to batch update a recordset. Eg. if you make multiple
> updates in a recordset it enables you to post them all at once.
> It doesnt allow you to pass in a script.
> Thanks
> Patrick
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:F00BB8E6-3D0F-46E1-9111-95F68AE3CF88@.microsoft.com...
>
>|||The simplest way to accomplish this is to build a large string with all of t
he
statements you wish to execute and execute the statement. Anything you write
in
QA, you can execute this way.
Thomas
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:On4RWZsTFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi,
> how would I go about sending a Update Script (with multiples Batches) via
ADO
> to an SQL 2000 Server.
> As much as I know the Command.CommandText can only take a single query.
> Thanks a lot
> Patrick
>|||Hi Thomas,
that is the answer I was looking for thanks.
I did't know that this would be possible.
I will try it out.
Good wishes
Patrick
"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:OLKhkLyTFHA.2768@.tk2msftngp13.phx.gbl...
> The simplest way to accomplish this is to build a large string with all of
> the statements you wish to execute and execute the statement. Anything you
> write in QA, you can execute this way.
>
> Thomas
>
> "Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
> news:On4RWZsTFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
Showing posts with label ado. Show all posts
Showing posts with label ado. Show all posts
Saturday, February 25, 2012
Monday, February 20, 2012
Multiple ADOQuery on same connection, performances go down because of serverside cursors...
Hi everyone,
I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.
The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic
On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.
In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.
It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.
I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.
More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.
Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.
I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.
So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?
Thanks to everyone for any future help !
Is this issue still alive? I have several years experience with Delphi and only a little with sql server, but I can never get ADO to perform quickly especially since our database holds pictures. That's why we opted to connect to sql server through the bordland db engine, since queries are always quick this way. Is this an option for you?Multiple ADOQuery on same connection, performances go down because of serverside cursors...
Hi everyone,
I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.
The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic
On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.
In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.
It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.
I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.
More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.
Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.
I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.
So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?
Thanks to everyone for any future help !
Is this issue still alive? I have several years experience with Delphi and only a little with sql server, but I can never get ADO to perform quickly especially since our database holds pictures. That's why we opted to connect to sql server through the bordland db engine, since queries are always quick this way. Is this an option for you?Multiple Ado Connections
Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!
Regards.They should close connections when the retrieving of data is completed
(or updates, or inserts are completed)
one connection per user is the best way to control connection leaks
(when developpers forget to close an unusefull connection)|||Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||programs are almost always "linear"
> open connection
> retrieve data1
> close connection
> open connection
> retrieve data2
> close connection
...
even if you've got 300 users there's rarely 300 connections
opened at the same time because they are closed most of the time
(except if users do big SELECTs on your database connections will
be opened longer)
Originally posted by TALAT
Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.
Regards.|||they could open a connection and leave it opened
but they should reuse this connection
instead of reopening new ones
this could be an other way to control the connection leaks
> start program : open ONE connection
> window1
> window2
> use program
> use program
...
> end program : close THE connection
in this case
Nb users = Nb connections
but if you have for example a max nb of connections at 100
only 100 users can connect at the same time
but with the other solution (open/close unuseful connection)
you can have more users than the max connections (100 in this example)
the users are not connected all the time of the use of the program
and they do not connect at the same time
if the max connections is reached a user just has to wait a little for a
connection to be liberated by an other user|||good luck with your consuming-connections-developper
just say to him that a connection is a very precious thing to waste
many users dreamed of having one free
and db-administrator cannot multiply them indefinitly
If ONE should use a connection
ONE should use it shortly
ONE should think of liberating it rapidly
ONE should carefully close it
ONE should know that ONE is not alone
For MANY use the program
Even if only ONE develops it|||I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!
Regards.
Regards.They should close connections when the retrieving of data is completed
(or updates, or inserts are completed)
one connection per user is the best way to control connection leaks
(when developpers forget to close an unusefull connection)|||Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||programs are almost always "linear"
> open connection
> retrieve data1
> close connection
> open connection
> retrieve data2
> close connection
...
even if you've got 300 users there's rarely 300 connections
opened at the same time because they are closed most of the time
(except if users do big SELECTs on your database connections will
be opened longer)
Originally posted by TALAT
Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
Thanx for the kind response.|||The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.
Regards.|||they could open a connection and leave it opened
but they should reuse this connection
instead of reopening new ones
this could be an other way to control the connection leaks
> start program : open ONE connection
> window1
> window2
> use program
> use program
...
> end program : close THE connection
in this case
Nb users = Nb connections
but if you have for example a max nb of connections at 100
only 100 users can connect at the same time
but with the other solution (open/close unuseful connection)
you can have more users than the max connections (100 in this example)
the users are not connected all the time of the use of the program
and they do not connect at the same time
if the max connections is reached a user just has to wait a little for a
connection to be liberated by an other user|||good luck with your consuming-connections-developper
just say to him that a connection is a very precious thing to waste
many users dreamed of having one free
and db-administrator cannot multiply them indefinitly
If ONE should use a connection
ONE should use it shortly
ONE should think of liberating it rapidly
ONE should carefully close it
ONE should know that ONE is not alone
For MANY use the program
Even if only ONE develops it|||I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!
Regards.
Multipe Inserts Causes Error - Please Help
I am currently using ADO to create a recordset, once I have selected my data (1 column called customer - datatype int - about 50,000 of them), I then post a transaction to each cutomer using a stored procedure. I am using a disconnect recordset for the initial ADO recordset and after each run of the store procedure I set everything to closed then nothing. I have plenty of resources available but I still get the following error.
17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
Can anyone help, sorry if this is not infomation I can post more if needed only to be honest I don't what to extra to post.
As a matter of interest I have also had this error when importing 1.7 millon records with DTS!!Have since found out this was due to a hardware issue, it seems that it relates to CPU clocking speeds and or Video cards.
Have replaced the cpu and motherboard in my case and the problem has not returned, and I have run extensive test and re-tests
17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
Can anyone help, sorry if this is not infomation I can post more if needed only to be honest I don't what to extra to post.
As a matter of interest I have also had this error when importing 1.7 millon records with DTS!!Have since found out this was due to a hardware issue, it seems that it relates to CPU clocking speeds and or Video cards.
Have replaced the cpu and motherboard in my case and the problem has not returned, and I have run extensive test and re-tests
Subscribe to:
Posts (Atom)