I am using an access database, I could create a temp table inside the database run the select's run the inserts, then delete all info from the table, is that what you are suggesting? I did index the date fields and got the original union query down to 30 seconds.
Travis Haley Haley Computer Solutions 2749 E. Nichols Cir Centennial, CO 80122 P: (303) 694-8050 F: (303) 779-0335 -----Original Message----- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 21, 2007 11:10 AM To: CF-Talk Subject: RE: table union Depending on your DBMS, I would suggest declaring a temp table or table variable (depending on how many records you are expecting). Do two separate inserts into the temp table. Then update any additional info you need into the table if applicable, (This allows you to have a smaller, better performing initial select). Then select out the final records from the temp table. If it takes you 50 seconds to run that select, you probably have some DB tuning to do. Are there indexes on the expen_Date and pur_Date_Purchased columns. ~Brad -----Original Message----- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Friday, September 21, 2007 12:01 PM To: CF-Talk Subject: table union I am trying to create a report that will show me all the checks I have written on my system in numerical order, the problem is I have my checks stored in two different tables in the database. These two tables split up expense checks and client checks, both tables have quite different data, however they do share some common data (check_Num), Ammount, and a date drafted, the other data isn't the same data types because I use a customer ID(number) to pull the name of the client for the check and I just type the name of the recipient(text) for the expense checks. Right now I am able to get all the checks in order with the following query. ----------------------------------- <cfquery name="rsGetPayments" datasource="# dsn#"> SELECT Check_Num, CheckAmount, expen_Date FROM tbl_Expenses WHERE expen_Date BETWEEN #CreateODBCDateTime(cFullStartDate)# AND #CreateODBCDateTime(cFullEndDate)# UNION SELECT Check_Num, pur_Amount, pur_Date_Purchased FROM tbl_HCFA_Purchase_Transactions WHERE pur_Date_Purchased BETWEEN #CreateODBCDateTime(cFullStartDate)# AND #CreateODBCDateTime(cFullEndDate)# ORDER BY Check_Num </cfquery> This gets me a unioned list sorted by check number in a date range supplied by a form. My question, is there a better way to union this data together because right now after I run this query I am going to have to run two separate queries in each table to get who the check was made out to and what the expense code was for the check. Right now this one query takes about 50 seconds to run and I imagine will only get longer with how many checks I write. Thanks in advance, Mallek ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289114 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
