AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so slow?          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message Otto Perdeck Guest Posted: Tue Nov 18, 2003 10:48 am    Post subject: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so slow? I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? Thanks Otto Back to top Joe Weinstein Guest Posted: Tue Nov 18, 2003 5:17 pm    Post subject: Re: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so s Otto Perdeck wrote: Quote: I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? What connection properties are you supplying? Are you using selectMethod=cursor? Are your queries involving varchar indexes? If so, add this property: p.put("sendStringParametersAsUnicode", "false"); Joe Weinstein at BEA Quote: Thanks Otto Back to top Otto Perdeck Guest Posted: Wed Nov 19, 2003 8:15 am    Post subject: Re: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so s The connection URL looks like "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor". SelectMethod cursor proved to be necessary because of other driver related problems. Varchar indices?? What's that? But my real question is how it can be that a query like SELECT weight,field_2,caseid FROM omegauser148_SAMPLES takes about 2 seconds, while it takes about 45 minutes to do SELECT weight,field_2, smptbl.caseid FROM omegauser148_SAMPLES AS smptbl, omegauser148_DATASETS AS dstbl WHERE dstbl.datasetid = 56 AND dstbl.caseid = smptbl.caseid and this only for the MS JDBC driver. When using J-SQL (NetDirect) for example, there is no significant difference in speed. -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote Quote: Otto Perdeck wrote: I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? What connection properties are you supplying? Are you using selectMethod=cursor? Are your queries involving varchar indexes? If so, add this property: p.put("sendStringParametersAsUnicode", "false"); Joe Weinstein at BEA Thanks Otto Back to top Joe Weinstein Guest Posted: Wed Nov 19, 2003 5:02 pm    Post subject: Re: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so s Otto Perdeck wrote: Quote: The connection URL looks like "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor". SelectMethod cursor proved to be necessary because of other driver related problems. Varchar indices?? What's that? nevermind... Your query doesn't use varchar search arguments, so you don't need to know... Quote: But my real question is how it can be that a query like SELECT weight,field_2,caseid FROM omegauser148_SAMPLES takes about 2 seconds, while it takes about 45 minutes to do SELECT weight,field_2, smptbl.caseid FROM omegauser148_SAMPLES AS smptbl, omegauser148_DATASETS AS dstbl WHERE dstbl.datasetid = 56 AND dstbl.caseid = smptbl.caseid and this only for the MS JDBC driver. When using J-SQL (NetDirect) for example, there is no significant difference in speed. This may well have to do with the (necessary for othe reasons) selectMethod setting. Please run a tiny 15-line standaone program that gets a driver connection without the selectMethod setting, and I'll bet you see the better performance. Are you using a PreparedStatement or a plain Statement? Joe Weinstein at BEA Quote: -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote in message news:3FBA5434.30507 (AT) bea (DOT) com... Otto Perdeck wrote: I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? What connection properties are you supplying? Are you using selectMethod=cursor? Are your queries involving varchar indexes? If so, add this property: p.put("sendStringParametersAsUnicode", "false"); Joe Weinstein at BEA Thanks Otto Back to top Otto Perdeck Guest Posted: Fri Nov 21, 2003 11:53 am    Post subject: Re: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so s Joe, Thx for thinking along, but as I already mentioned, there is no problem with a simple select, but there is with a slightly more complex one. I'm using the same URL all the time, which includes the SelectMethod=Cursor (and this for good reasons, because the #$&^#*$& driver otherwise refuses multiple open ResultSet's). I'm not searching for a work-around, that I already have. I'm looking for an explanation. How can it be that a small change in the SQL makes such a huge difference in performance? And how can it be that the MS JDBC driver is the only one (as far as I know) to have this behaviour? I also would like to see confirmation from other users that this MS JDBC driver is very lousy. -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote Quote: Otto Perdeck wrote: The connection URL looks like "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor". SelectMethod cursor proved to be necessary because of other driver related problems. Varchar indices?? What's that? nevermind... Your query doesn't use varchar search arguments, so you don't need to know... But my real question is how it can be that a query like SELECT weight,field_2,caseid FROM omegauser148_SAMPLES takes about 2 seconds, while it takes about 45 minutes to do SELECT weight,field_2, smptbl.caseid FROM omegauser148_SAMPLES AS smptbl, omegauser148_DATASETS AS dstbl WHERE dstbl.datasetid = 56 AND dstbl.caseid = smptbl.caseid and this only for the MS JDBC driver. When using J-SQL (NetDirect) for example, there is no significant difference in speed. This may well have to do with the (necessary for othe reasons) selectMethod setting. Please run a tiny 15-line standaone program that gets a driver connection without the selectMethod setting, and I'll bet you see the better performance. Are you using a PreparedStatement or a plain Statement? Joe Weinstein at BEA -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote in message news:3FBA5434.30507 (AT) bea (DOT) com... Otto Perdeck wrote: I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? What connection properties are you supplying? Are you using selectMethod=cursor? Are your queries involving varchar indexes? If so, add this property: p.put("sendStringParametersAsUnicode", "false"); Joe Weinstein at BEA Thanks Otto Back to top Joe Weinstein Guest Posted: Fri Nov 21, 2003 4:47 pm    Post subject: Re: Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so s Otto Perdeck wrote: Quote: Joe, Thx for thinking along, but as I already mentioned, there is no problem with a simple select, but there is with a slightly more complex one. I'm using the same URL all the time, which includes the SelectMethod=Cursor (and this for good reasons, because the #$&^#*$& driver otherwise refuses multiple open ResultSet's). You are missing my point. I think it is an effect of the more complex query and the way the DBMS must create a cursor for it, when operating in that mode. The driver itself does nothing to your SQL. Simple or complex, the driver just sends it directly to the DBMS. So, it you have the time, just run a plain non-cursor-mode connectiom and a single plain statement with your more complex query. Let us know if it is also slower, or if it's as fast as it should be. Joe Weinstein at BEA Quote: I'm not searching for a work-around, that I already have. I'm looking for an explanation. How can it be that a small change in the SQL makes such a huge difference in performance? And how can it be that the MS JDBC driver is the only one (as far as I know) to have this behaviour? I also would like to see confirmation from other users that this MS JDBC driver is very lousy. -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote in message news:3FBBA21E.60804 (AT) bea (DOT) com... Otto Perdeck wrote: The connection URL looks like "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor". SelectMethod cursor proved to be necessary because of other driver related problems. Varchar indices?? What's that? nevermind... Your query doesn't use varchar search arguments, so you don't need to know... But my real question is how it can be that a query like SELECT weight,field_2,caseid FROM omegauser148_SAMPLES takes about 2 seconds, while it takes about 45 minutes to do SELECT weight,field_2, smptbl.caseid FROM omegauser148_SAMPLES AS smptbl, omegauser148_DATASETS AS dstbl WHERE dstbl.datasetid = 56 AND dstbl.caseid = smptbl.caseid and this only for the MS JDBC driver. When using J-SQL (NetDirect) for example, there is no significant difference in speed. This may well have to do with the (necessary for othe reasons) selectMethod setting. Please run a tiny 15-line standaone program that gets a driver connection without the selectMethod setting, and I'll bet you see the better performance. Are you using a PreparedStatement or a plain Statement? Joe Weinstein at BEA -Otto "Joe Weinstein" <joeNOSPAM (AT) bea (DOT) com> wrote in message news:3FBA5434.30507 (AT) bea (DOT) com... Otto Perdeck wrote: I found out that the MS JDBC driver is terribly slow when doing anything slightly more complex than "select a,b,c from x". Keeping it that simple gives a fast response. But adding even a simple where clause makes it very, very slow (from 2 seconds suddenly down to 45 minutes). We've found a work-around by first creating a temporary table with the select result, then selecting all from that temporary table (with a really simple selection). But this is pretty bizarre. No other JDBC driver we're using requires such measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and we're using the same SQL in all cases. Anyone any explanation? What connection properties are you supplying? Are you using selectMethod=cursor? Are your queries involving varchar indexes? If so, add this property: p.put("sendStringParametersAsUnicode", "false"); Joe Weinstein at BEA Thanks Otto Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.