CF'ers at Apple's WWDC '07?
There's gotta be a few of us going... Hit me up, siculars /a-t\ gmail ~d~o~t~ com! ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280582 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: slow query in cf but fast in Sql2k5 management studio.
thanks doug, SELECT a,b,c,d,e,f FROM tbl1 WHERE 1=1 AND ( b = txt1 AND c = int1 ) OR ( d IN (txt2,txt3) AND e = txt4) ORDER BY f the only thing specific that i can think of regarding this query is that columns d and e return long text (type text in sql2k5). Can you post the query? That would help alot. Doug B. - Original Message - From: Sicular, Alexander [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Friday, January 05, 2007 7:24 AM Subject: slow query in cf but fast in Sql2k5 management studio. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265781 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: slow query in cf but fast in Sql2k5 management studio.
thanks porter, i took your advice and just set up a plain cfm page with four versions of that query. 1. no cfqueryparam all columns Time=31ms, Records=210 2. no cfqueryparam no txt columns Time=31ms, Records=210 3. cfqueryparam all columns Time=17297ms, Records=210 4. cfqueryparam no txt columns Time=16359ms, Records=210 turns out the cfqueryparam is giving me the problem here. any thoughts? i use cfqueryparam mostly everywhere as a rule and never ran into this problem. like, why here, why now? -alex How much data is it returning? A few rows, a few dozen, thousands, hundreds of thousands, millions. Sql2k5 just displays the results of the query. CF transforms the query into a java.util.Map which results in processing time on the CF server in addition to the processing time on the SQL server for each query. Also adding an appropriate blockfactor may speed this up as the driver may be sending one row at a time back to CF instead of as much as it can. Are you sure it is the query that is taking a long time or is it the transformation of the query into whatever view you are making out of it? Create a template with nothing in it but a cfquery call with the sql. Do not dump it or anything and see whether the code that renders the query results into something else is the culprit. If you can rule out CF processing as the issue, post the execution plan created. Maybe you are missing indexes etc. Try running the query with out using cfqueryparam if you are and compare the timing. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265816 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: slow query in cf but fast in Sql2k5 management studio.
hi jochem, yes, i am using nvarchar. column types are: a int b nvarchar c int d text e text f datetime fyi, how would i set that param in the jdbc url? here is my current: jdbc:sqlserver:// server1 : 1433 ;DatabaseName=db1 -alex 1. no cfqueryparam all columns Time=31ms, Records=210 2. no cfqueryparam no txt columns Time=31ms, Records=210 3. cfqueryparam all columns Time=17297ms, Records=210 4. cfqueryparam no txt columns Time=16359ms, Records=210 turns out the cfqueryparam is giving me the problem here. any thoughts? i use cfqueryparam mostly everywhere as a rule and never ran into this problem. like, why here, why now? Are you using any 'N' fields in your database? Have you set sendStringParametersAsUnicode in your JDBC URL? If not, set it to false and try again. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265827 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: slow query in cf but fast in Sql2k5 management studio.
thanks dave, i tried various combinations of cfqueryparam usage to no avail. seems to be all or nothing. will try the profiler later tonight. thanks for the link... -alex turns out the cfqueryparam is giving me the problem here. any thoughts? i use cfqueryparam mostly everywhere as a rule and never ran into this problem. like, why here, why now? I don't know. However, there are a couple of approaches you can use to find out more. First, you might see if it's one specific CFQUERYPARAM that's causing the problem. Second, you might use SQL Profiler to capture your queries. You could then compare their execution plans. http://www.sqlservercentral.com/columnists/RDyess/obtainingqueryexecutionpla nsthroughsqlprofilertrac.asp Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265829 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Get last inserted ID
dont know about mysql, but this is how i do it in mssql2k : cfquery name=getMaxObject datasource=#dsn# SET NOCOUNT ON INSERT INTO Objects (EmployeeID, patientID, ObjectTypeID) VALUES (cfqueryparam cfsqltype=CF_SQL_INTEGER value=#session.whoami#, cfqueryparam cfsqltype=CF_SQL_INTEGER value=#form.patientid#, cfqueryparam cfsqltype=CF_SQL_INTEGER value=27) SELECT @@identity as maxObjectID SET NOCOUNT OFF /cfquery then to get the value i do #getMaxObject.maxObjectID#. give it a shot? -alex Is this the best way to do this: cftransaction cfquery name=InsertAddress datasource=#DSN# INSERT INTO Address (City) VALUES (cfqueryparam value=#City# cfsqltype=cf_sql_integer /) /cfquery cfquery name=getInsertedID datasource=#DSN# SELECT MAX(AddressID) as MaxID FROM Address /cfquery /cftransaction I'm using MySQL. Cheers, Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229392 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
ot: left outer join via derived table, mssql2k
Hi sql Gurus, I have three tables. Briefly, lets call them T, O and P. Related thusly: T.objectid = O.objectid; O.patientid = P.patientid I am trying to return data from P and a derived table consisting of T and O like so: SELECT P.patientid, P.lastname, P.firstname, DT.datestamp FROM patientcore P, (select O.patientid, O.datestamp, T.dos from objects O, tbl_dailylog T where T.dos BETWEEN '9/20/2004' AND '9/21/2004' and O.objectid=T.objectid) DT WHERE p.patientid=dt.patientid The above query works as expected. However what I would really like to do is replace P.patientid=DT.patientid with p.patientid*=DT.patientid. But when I do that I get the following: Server: Msg 303, Level 16, State 1, Line 1 The table 'objects' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause. So... what do I need to do to make this work? Ultimately I would like to return columns from O. My interim workaround is to squash T from the derived table and query O.datestamp instead of T.dos. Thanks in advance, Alex ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184948 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: ot: left outer join via derived table, mssql2k
edit Ultimately I would like to return columns from T. /edit ...alex ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184949 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
ot: distinct count query; mssql2k
hi all, i am trying to query a table for most often inserted values and have stumbled into a roadblock. the query i have so far works as expected: select top 20 field1, COUNT(field1) AS Expr1 FROM tbl1 GROUP BY field1 order by expr1 desc the problem is that i would also like to return a second column that just so happens to be of text type. mssql2k will not accept a text type column in the group by clause and i have tried to cast it as a varchar with no luck. any thoughts? tia, alex [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: ValueList() Error
I have also just come across this problem only yesterday. I was using valuelist inside a listfind like so : listfind(valuelist(query.booleancolumn),1) I had to change 1 to true to get it to work as expected. This only happens with bit column from mssql2k. when dumping the query that contains the boolean field it dumps as 0 / 1. Seems a problem with the valuelist function. Very unsettling to say the least. Filed a bug report with mm via their website. Lets see what happens... gl, alex OK, We have a very strange issue with a resultsetwe have the following coming back from the DB... bDisplay (Column Name) 0 1 0 0 0 1 As you can see the data in the column is simple 1s and zeros (bit Datatype)..now for the problem : If I use valuelist(rsExhibitorSettings.BDISPLAY) the values coming out are as follows False, true, false, false, false, true!??!?! Now what the heck is up with that?!!?!? N [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re:cfquery performance suggestions
maybe you could use category=left(category, 'p', 1) instead of like. depending on your database syntax may vary. i find that like is the slowest operation you can do. gl, alex I'm using the following query to find all the catagories in a table that start with a certain letter :- SELECT distinct CATEGORY FROM dbo.mainfile where CATEGORY like 'P%' order by CATEGORY ASC I have an index based on Category but it runs slow (it's a v. large table - and it't can't be redesigned). Any suggestions for a better query to get the results ? - btw I'm very much a SQL novice Cheers Richard [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
