Query very slow via JDBC compared to Query Analyzer

I am trying out the JDBC driver SP3 for data access in a Java application,
and I find that some of the queries I had tested in Query Analyzer are
returning very slowly in my Java application. The queries not extremely
complex (just a couple joins), and they return small datasets (on the order
of 2-20 rows with 2 columns each).
In one example, I am waiting about 20 seconds for a query to return in my
Java application where it took less than one second in Query Analyzer.
I'm not really sure where to look to address these performance issues. Is
there something obvious I might have wrong on my connection string? I connect
to SQL Server over 100Mb Ethernet.
I'd appreciate any help!
Thanks
OK, it seems I did not choose very good search parameters when I looked for
answers previously... turns out the SendStringParametersAsUnicode as the
culprit. Sorry =) But thanks to those who posted previously.
"bbabey" wrote:

> I am trying out the JDBC driver SP3 for data access in a Java application,
> and I find that some of the queries I had tested in Query Analyzer are
> returning very slowly in my Java application. The queries not extremely
> complex (just a couple joins), and they return small datasets (on the order
> of 2-20 rows with 2 columns each).
> In one example, I am waiting about 20 seconds for a query to return in my
> Java application where it took less than one second in Query Analyzer.
> I'm not really sure where to look to address these performance issues. Is
> there something obvious I might have wrong on my connection string? I connect
> to SQL Server over 100Mb Ethernet.
> I'd appreciate any help!
> Thanks

Query Types in Query Designer

I am just beginning to use SQL Server(MSDE), Access 2003 adp projects in a
web application I am developing. In using the query designer I am given the
option of developing a View or a Function in addition to a Stored Procedure.
I know about Stored Procedures but I have no information on when and how to
use Views or Functions. Can someone give me some insight on what
circumstances would cause you to select one query type over another and/or
point me to some documentation that will?
Through trial and error I have found that you can’t have a computed column
in a Function but you can in a View and you can sort in a Function but you
can’t in a View. I am investing quite a bit of effort into this project and
I don’t want to get down the road too far and find out that I have made some
basic mistake that will require substantial rework.
Any help you can give will be greatly appreciated.
Ray Cannon
EIS, Inc.
Access is generally not the best choice for web applications. It is
also not the best choice of development tools for a SQL Server
database. It is a better idea to purchase the Developer edition of SQL
Server ($49) that has the client tools and use it to create your SQL
Server objects. Use MSDE only when it comes time to deploy your
application. The license agreement for the Developer edition prohibits
its use in a production environment, but it has everything you need to
create, test and secure SQL Server databases. SQL Books Online is a
good source of information on creating SQL Server objects, as are
third-party books. You can download the latest version at
http://www.microsoft.com/sql/techinf...2000/books.asp
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 4 Aug 2004 12:15:04 -0700, "RayC"
<RayC@.discussions.microsoft.com> wrote:

> I am just beginning to use SQL Server(MSDE), Access 2003 adp projects in a
>web application I am developing. In using the query designer I am given the
>option of developing a View or a Function in addition to a Stored Procedure.
>I know about Stored Procedures but I have no information on when and how to
>use Views or Functions. Can someone give me some insight on what
>circumstances would cause you to select one query type over another and/or
>point me to some documentation that will?
>Through trial and error I have found that you cant have a computed column
>in a Function but you can in a View and you can sort in a Function but you
>cant in a View. I am investing quite a bit of effort into this project and
>I dont want to get down the road too far and find out that I have made some
>basic mistake that will require substantial rework.
>Any help you can give will be greatly appreciated.

Query Tuning

I have a VB application and with the help of record sets i retrieve as many rows from the SQL Server 2000 DB.

The query that is in the SP is nested and so while retrieving it took 2 secs for a very few records and so I changed the query to a simple query but with more conditions on the WHERE clause..

But now also it takes the same 2 secs and i would like it to be reduced to milliseconds..

Any ideas are most welcome...

TIA,
NishaIf you can cut-n-paste the SQL into Query Analyzer you can Display the Query Plan (Ctrl+L) and see what Query Plan it's using, so you can determine whether indexes would help.
- Andy Abel|||It will be better if u put the script, u can use the Profiler to see the exact duration the Sql is taking,
Dont use Select * , better give the exact column names , and in the where condition u try to use id columns.
-Ashok|||Originally posted by iamnisha
I have a VB application and with the help of record sets i retrieve as many rows from the SQL Server 2000 DB.

The query that is in the SP is nested and so while retrieving it took 2 secs for a very few records and so I changed the query to a simple query but with more conditions on the WHERE clause..

But now also it takes the same 2 secs and i would like it to be reduced to milliseconds..

Any ideas are most welcome...

TIA,
Nisha

Reply-

Try to use 'primary key with clustured indexs' keys reference. after the where clause.

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy B
Hi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B
|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_P RECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Dat
a
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex dat
a
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of t
he
> implimentation. I need to identify the complete file structure in order t
o
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run pe
r
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for you
r
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
--
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:
> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

Query to Get and Set Image data type Value in SQL Query

Hi

I'm using Sharepoint Services in my application.My database is SQl server 2005 My Sharepoint site using one database there is one table called docs. In this table one column is called MetaInfo and its datatype is Image.
My Question is How to write a Query to Get and Set Image data type Value.I want to execute the Query in SQL Itself. If anyone knows the answer Please let me to know.
Thanks

Regards,
Vinoth

It is not clear what you want to do exactly. But you can just use regular insert/update statements to modify image data. You need to specify the image data as a binary string. Ex:

update t
set imagecol = 0xfff3030939393910

If you have the data in another table, then you can use UPDATE with the T-SQL syntax to copy value from one table to another. Ex:

update t
set imagecol = t1.imagecol
from t1
where t1.i = t.i

Also, you can have SPs with text/ntext/image data type as parameters so you can pass these values directly from the client side and use them in statements like above. Note that you cannot manipulate the variable directly in T-SQL.

For more control on the modifications, you can use UPDATETEXT. See also PATINDEX and SUBSTRING topics in Books Online.

Query to Backup Current Database

Hi

Is there any query to backup and restore a database?In my application User has to Backup the current database and restore them at any time.I just need query which backup the database in a specified location and restore it.

Thanks

See BACKUP and RESTORE on Books Online..|||

In the simplest forms:

BACKUP DATABASE myDb TO DISK = 'X:\myDb.BAK'

RESTORE DATABASE myDb FROM DISK = 'X:\myDb.BAK'

There's a little bit more to it though, so please refer to BOL (Books On Line) for additional info on backup and restore, so you can decide upon the exact syntax to use.

/Kenneth

|||

Thanks Mr.Kenneth.Plz suggest some online books or give link regarding backup and restore...Plz

Finally thanks a lot Mr.Kenneth.

|||thanks Manivannan Suggest some online books or links|||when they mention BOL( Books Online), they are referring to Microsoft's Online Documentation for SQL Server 2005.

the homepage for it is here

the download for the english(may 2007) version is here

Query timeout problems

Front End: Access 2000 Project (.adp)
Back End: SQL Server 2000

I have an application that keeps timing out. I have opened the
DataLink properties in the front end (File, Connection, Advanced tab)
and set the timeout to 999. But the connection still occasionally
times out.

Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
etc.)

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0501310730.3f8e6ed9@.posting.google.c om...
> Front End: Access 2000 Project (.adp)
> Back End: SQL Server 2000
> I have an application that keeps timing out. I have opened the
> DataLink properties in the front end (File, Connection, Advanced tab)
> and set the timeout to 999. But the connection still occasionally
> times out.
> Any ideas? Some of the SQL is pretty horrible (multiple sub-queries
> etc.)
> TIA
> Edward
> --
> The reading group's reading group:
> http://www.bookgroup.org.uk

The timeout you changed is the connection timeout, so it won't affect
timeouts caused by long-running queries. Depending on what connection
library you're using (ODBC, ADO), you should be able to change the general
timeout value, but I have no idea how - perhaps an Access group might give
more help.

In ay event, if the timeouts occur during different queries, or the same
queries with different parameters, you'll probably have to try to identify
the problem queries more exactly - Profiler would be a good place to start.

Simon

Query Timeout Issue with .NET

Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w with no replies I came here and have had great replies
within the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)

> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Timeout

Hello,
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>

Query Timeout

Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:

> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA