JDBC 1.2 - Decreased Performance When Using Parameters
Nov 22, 2007
Hi all.
Context: SQL 2000, JDBC 1.2
I have a query with 9 parameters that run as follows (approximate timing):
1. Using prepared statement: 1,7 sec
2. Re-using the same statement: 1,7 sec
3. Using prepared statement, no parameters (the params are hardcoded): 20 ms
4. Using statement+resultset+rs.getFirst: 20 ms
5. from inside SQL analyzer: instantly
So, I observe a problem when using parameters. Do I miss something, do I have to do something?
If the actual query may help, I will post it promptly.
Thx in advance
Denis
View 5 Replies
ADVERTISEMENT
Mar 4, 2004
I'm trying to store files such as pdfs in my SQL Server as blobs for a particular .NET application and I'm noticing a rather large performance hit when compared to if the pdf was simply stored in the web server's file system.
I have a pdf of 7MB. If this is on a web server it takes roughly 9 seconds to download. When I store the same pdf in my database and then write it onto the page using the code below it takes roughly 40 seconds to display. Can anyone help me to improve this performance difference? Should I not be using this method to store large files?
Dim PdfCol As Integer = 0 ' the column # of the BLOB field
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
Dim mypdf(dr.GetBytes(PdfCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(PdfCol, 0, mypdf, 0, mypdf.Length)
End If
Response.Clear()
Response.Buffer = True
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(mypdf)
Response.Flush()
Response.Close()
View 4 Replies
View Related
Mar 13, 2006
Hi,
I have migrated my database from sql server 2000 to sql server 2005,
for migration i have backed up 2000 database and restored that into sql server 2005 databse.
when we are running following insert query is 50% slower in sql server 2005.
truncate table Table1
print getdate()
INSERT INTO Table1 SELECT CONVERT(INTEGER,cust_id ), store_id, MAX(Buydate), COUNT(*) FROM Table2 (NOLOCK) GROUP BY cust_id, store_id
print getdate()
How to increase the performance for the query.
Thanks in advance.
Regards
Manoj
View 7 Replies
View Related
Aug 18, 2006
I cannot get performance out of sql server 2005 through jdbc connections.
I have used multiple drivers against 2000 and 2005. 2000 always comes out on top.
I installed the os the same, configured the raid array the same, configured the os the same, configured the database the same, installed the software the same, etc.
It seems to come down to the jdbc driver and the way the database handles batch requests from jdbc. Is there some configuration in 2005 that I need to alter to improve the performance of batched inserts through the jdbc driver?
View 5 Replies
View Related
Nov 6, 2007
I have a specific job that should be run with a decreased memory usage when the workload is heavy on the SQL Server.
This is a heavy job that has no specific requirement when it comes to response time.
It is important that the rest of the application shouldn’t be affected with longer response time when this job is running.
How can this job bee handled from the application, without having to create a separate batch job.
View 1 Replies
View Related
Apr 9, 2008
Hi,
I have a user defined function which has 5 parametes to pass, now while running the Table Valued Function using hardcoded parametes value its taking only 10 seconds , but while running the UDF by declaring the parametes value and
assigned them value its taking more than 3 minutes.
here is the example
First case
select * from UDF_Test('A',76,'C',987,''X')
This one taking only 10 minutes to run.
Second case
Declare @P1 varchar,
@P2 Int,
@P3 varchar,
@P4 int,
@P5 varchar
SET @P1 = 'A'
@P2= 76
@P3='C'
@P4 = 987
@P5='X'
Select * from TVF_Test(@P1,@P2,@P3,@P4,@P5) as test
where test.column1=@P1
Any help will be appreciated.
Thanks
View 6 Replies
View Related
Mar 22, 2007
Dear all
Can any one provide me details of the parameters to be monitored in sql server 2005
Currently i am monitoring
SQLServer : Buffer Manager : Buffer Cache Hit Ratio
SQLServer : Databases : Log Flushes / Sec
SQLServer : Databases: Transactions / Sec
SQLServer : Access Methods : Page Splits / Sec.....
1) Please provide if any other relevant parameters to be monitored by perfmon.
2) Which are the most essential parameters to be monitored.
3) Is there any query or trace that will provide me performance details in SQL.
4) Can i have a query that get me the most resource consuming process or query in sql server.
5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.
It would be very helpful if you guys could provide me help on these.
Thanks in advance
View 3 Replies
View Related
Apr 14, 2008
I have read similar posts to this, but I am still having problems.
I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using
MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"
"WebRoot/WEB-INF/web.xml":
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
"WebRoot/META-INFcontext.xml":
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/DefaultDS"
auth="Container"
type="javax.sql.DataSource"
username="tec"
password="tec"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
url="jdbcqlserver://localhost:1433/tec;databaseName=tec;user=tec;password=test;"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
Classpath:
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/dom.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxen-full.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxp-api.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jdbc2_0-stdext.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sqljdbc.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jstl.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/mail.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sax.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/saxpath.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/standard.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xalan.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xercesImpl.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>
Code to connect:
import java.io.Serializable;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public int testConnect(){
Connection conn = null;
InitialContext ctx = null;
java.sql.Statement stmt = null;
try {
ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/DefaultDS");/*This is generating the Cannot load JDBC driver class... error*/
conn = ds.getConnection();
stmt = conn.createStatement();
return CONSTANT.SUCCESS;
} catch (Exception e) {
return CONSTANT.FAILURE;
}finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
if (ctx != null)
ctx.close();
} catch (Exception ex) {
// do nothing
return CONSTANT.FAILURE;
}
}
}
Any ideas would be greatly appreciated.
View 17 Replies
View Related
Jul 17, 2006
I have run into a very odd issue with the performance of one of our stored procedures. The SP seems to perform very poorly if I embed the parameters directly into the query. If I declare local variables to hold the parameter values, the SP runs a lot faster.
This stored procedure takes about 26 seconds:
CREATE PROCEDURE dbo.jk_ReportData_Get
@startDate datetime, @endDate datetime, @entityID int, @reportID int
AS
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
When I change it to the following, it takes less than one second:
CREATE PROCEDURE dbo.jk_ReportData_Get
@temp1 smalldatetime, @temp2 smalldatetime, @temp3 int, @temp4 intAS
declare @startDate datetimeset @startDate = @temp1declare @endDate datetimeset @endDate = @temp2declare @entityID intset @entityID = @temp3 declare @reportID intset @reportID = @temp4
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
Can anyone explain this please??? :) Notice it is the same query in both cases. The only difference is in the parameters...
View 21 Replies
View Related
Jan 28, 2008
Hi all,
I have a report which uses two different datasets. The first one is used to get the required data and the second one is to get the customer name to be used in a parameter.
I had a major performance issue with the report as the first dataset is very complex. And after I did a lot of SQL tuning to the first dataset, it is now working fine with an acceptable performance.
The problem now is when I tried to add a condition in this dataset to be used by the parameter like
WHERE CUSTOMER_NAME IN (@CustName )
The performance of the report is getting very bad again and the report can take more than 15 min. to get the result.
The second dataset is a simple query that is used to get the customer name from the customer table so I don€™t think it is the problem.
Any ideas? Do you think it has anything to do with the SSRS parameters.
Thanks all.
View 1 Replies
View Related
Jan 7, 2008
I'm using sql2000. VB2003.
My report uses the following parameters
WHERE (@Unit='' or a.unit = @Unit) and (@WO='' or a.wo = @WO)
It takes about 23 seconds to run if I type in 71173 as Unit leaving WO blank.
If I edit the SQL to be Where a.unit=71173 it execustes in 1 second.
Where am I going wrong?
I read some of the threads but I couldn't get a clear idea if this is normal or not.
Card Gunner
View 3 Replies
View Related
Aug 8, 2006
Hi all,
We've just stumbled on a 1.0 version incompatibility with the JDBC specs.
Problem: A table with SMALLINT column. According to JDBC specs version 3.0
(p.B-179) and 4.0 (p.221)), the value should be converted to Integer type.
Unfortunatelly we get a Short object :(
Now, I remember, this case was also affecting old JSQLConnect driver from
DataDirect. Could that problem sneak to new MS driver too?
Please let me know any resolution to this problem if exists.
The issue has not been fixed in CTP 1.1 version. Any ideas if it can be fixed??
Cheers,
Piotr
View 1 Replies
View Related
Mar 12, 2008
I've got an import app written in Java. One table I'm importing from contains 22 million records. When I run the app in a 2000 environment, I have my max heap set at 512, and the table gets imported. When I run in a 2005 environment, I have to change the max heap to 1152 or it will error out with a similiar error:
com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:854,269,999. JVM total memory size:1,065,484,288. (<--this is with max heap at 1024)
what is the difference between the 2000 and 2005 JDBC that I have to set max heap in one and not the other?
View 3 Replies
View Related
Jul 8, 2004
Hi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.
I have set the dynamic paramters option on, and use collation compatible.
View 5 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Nov 26, 2001
Are there separate jdbc drivers for Sql Server 7 or should I use the 2000 drivers?
View 1 Replies
View Related
Aug 6, 2001
Is anyone out there using Unix/Java/Sun app server to connect to SQL Server via JDBC? If so, what drivers are you using? We are currently testing with WebLogic's BEA JDBC driver. Does anyone have any feedback on it?
View 2 Replies
View Related
Feb 6, 2004
Is oracle and Microsoft JDBC drivers are same? Oracle 9i comes with JDBC, can I use the same driver to access the Microsoft SQL Server?
View 1 Replies
View Related
Jun 1, 2004
Hi
I just downloaded the Microsoft JDBC Driver for SQL Server 2000. Now it tells me SQL Server 7 is not supported. :o I already checked the JDBC Driver list on suns site. Its quite long and only states MS SQL Server without any version number. Can you recommend a particular driver? Preferrably without any cost. :D
Or am I better of with the jdbc/odbc bridge?
Thanks
Shabassa
View 1 Replies
View Related
May 21, 2008
Hi
I have a java program which uses sql.It supports sql 2000 and 2005.The user can enter a command from which he can choose to what server type he wants to connect.It is something like :
" c:connect [jdbc driver for 2005] [server] "
Now i have to do some different operations when user connects to 2000 and other operations when user connects to 2005.
The problem is that when using jdbc driver for 2005 he can also connect to mssql server 2000.
How can i find out to what server type he is connecting to?
i hope i explained it clearly enough
thanks
View 1 Replies
View Related
Dec 7, 2004
Hi,
Does SQL Server Express ship with a JDBC driver? ... Hardly so, but is it available somewhere?
Rgds and thanks, PP
View 3 Replies
View Related
Mar 8, 2006
Hi guys,
Where can I download the JDBC Driver for our MS SQL Server 7 ?
Thank you.
View 3 Replies
View Related
Apr 5, 2006
this url is having problem while making connection to SQL Server
jdbc:XXX:sqlserver://<host>:1433;DatabaseName=<name>
error:
"The requested instance is either invalid or not running."
View 5 Replies
View Related
Jul 23, 2005
Hello all,I have a question about the SQL Server JDBC driver. I was wondering ifanyone knows what the default prefetch size is (in number of rows).Also, does anyone know if an entire packet is sent (i.e. padded withnull values) if there is not enough data to fill it?Thanks for your help!ty
View 2 Replies
View Related
Jul 20, 2005
I downloaded the latest version on JDBC from Microsoft and I got problems.Indeed, when fetching a smallint from SQL Server i got instead an Integer.How can i do to avoid such a problem?Some hints?many tanks, gaetano
View 1 Replies
View Related
Jul 20, 2005
I have a MSDE database that I am trying to connect to with JDBC. I don'thave SQL Server installed. Is this possible? When I try to connect I getthe error:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver forJDBC][SQLServer]Login failed for user 'sa'. Reason: Not associated with a trusted SQL Serverconnection.From what I've read on the web it looks like I need to set the securitysettings to allowfor SQL authentication as well as NT authentication. Is that correct? Andcan thatbe done with just MSDE?I think that I have MSDE 2000 installed (it actually came with a differentapplication).I just downloaded the latest SQL Server JDBC driver.Thanks,Bruce
View 1 Replies
View Related
Jul 20, 2005
I need a free, redistributable JDBC driverfrom MS SQL Server. (MSDE2000a)
View 2 Replies
View Related
Jul 20, 2005
Dear all,Where can I find JDBC for MS SQL server?Thx for reply.Victoria
View 2 Replies
View Related
