More SQL Temp Tables, Please!

jlego

Ran into a function wrote by a previous employee first thing this morning, never really seen anything like this

Dim adocommand As New ADODB.Command

Dim adoconnect As New ADODB.Connection

Dim report As New arptAccount306090

adocommand.CommandText = "DROP TABLE IF EXISTS balance_acc"

adocommand.CommandType = adCmdText

adocommand.ActiveConnection = adoconnect

adocommand.Execute

adocommand.CommandText = "DROP TABLE IF EXISTS calc"

adocommand.CommandType = adCmdText

adocommand.ActiveConnection = adoconnect

adocommand.Execute

adocommand.CommandText = "DROP TABLE IF EXISTS contract_delen"

adocommand.CommandType = adCmdText

adocommand.ActiveConnection = adoconnect

adocommand.Execute

adocommand.CommandText = "DROP TABLE IF EXISTS oldcalc"

adocommand.CommandType = adCmdText

adocommand.ActiveConnection = adoconnect

adocommand.Execute

adocommand.CommandText = "DROP TABLE IF EXISTS ACassociates"

adocommand.CommandType = adCmdText

adocommand.ActiveConnection = adoconnect

adocommand.Execute

adocommand.CommandText = "Create temporary table contract_delen SELECT ( (Amount_Monthly + Amount_Weekly + Amount_Semi_Monthly) ) as contractually_due , id as account_card_header_id, ( if( DATE_FORMAT(Date_Of_Purchase,'%d') > DATE_FORMAT(now(),'%d') , 1 , 0) + PERIOD_DIFF(DATE_FORMAT(now(),'%Y%m'), DATE_FORMAT(Date_Of_Purchase,'%Y%m')) ) as monthes FROM account_card_header"

adocommand.Execute

adocommand.CommandText = "CREATE INDEX id_index ON contract_delen(account_card_header_id)"

adocommand.Execute

adocommand.CommandText = "Create temporary table oldcalc SELECT max(date) as last_pay, id, account_card_header_id FROM account_card_detail where credits > 0 and date <= '" & Format(Options.DTPicker1, "YYYY-MM-DD") & "' group by account_card_header_id"

adocommand.Execute

adocommand.CommandText = "CREATE INDEX id_index ON oldcalc(id)"<br>
adocommand.Execute<br><br>

 adocommand.CommandText = "Create temporary table calc  select  oldcalc.last_pay, sum(account_card_detail.credits) as oldcredits,  oldcalc.account_card_header_id FROM oldcalc  left join account_card_detail on oldcalc.account_card_header_id = account_card_detail.account_card_header_id  and   oldcalc.last_pay = account_card_detail.date group by account_card_detail.account_card_header_id "<br>
adocommand.Execute<br><br>

adocommand.CommandText = "CREATE INDEX id_index ON calc(account_card_header_id)"<br>
adocommand.Execute
<br><br>
adocommand.CommandText = "Create temporary table balance_acc SELECT sum(temporary_pay ) as temppays , round(sum(charges + fee - credits),2) as balance, sum(fee) as fees, sum(charges) as charges, sum(credits) as credits, account_card_detail.account_card_header_id , if (round(sum(charges - credits),2) = 0 , 0 , 1) as card_number, calc.last_pay , calc.oldcredits FROM account_card_detail left join calc on calc.account_card_header_id = account_card_detail.account_card_header_id  where date <= '" & Format(Options.DTPicker1, "YYYY-MM-DD") & "'  group by account_card_detail.account_card_header_id"<br>
adocommand.Execute
<br><br>
 adocommand.CommandText = "CREATE INDEX id_index ON balance_acc(account_card_header_id)"<br>
adocommand.Execute<br><br>
   
adocommand.CommandText = " create temporary table ACassociates SELECT account_card_header.id , account_card_header.associate_id, group_concat(system_users.initals , ' ') as associates  FROM account_card_header  left join system_users on account_card_header.associate_id  like  concat('%;' , system_users.id , ';%') or account_card_header.associate_id  like  concat( system_users.id , ';%')  left join balance_acc on balance_acc.account_card_header_id = account_card_header.id where balance <> 0 group by  account_card_header.id "<br>
adocommand.Execute<br><br>

adocommand.CommandText = "CREATE INDEX id_index ON ACassociates(id)"<br>
adocommand.Execute
<br><br>
adocommand.CommandText = "DROP TABLE IF EXISTS old_pay"<br>
adocommand.CommandType = adCmdText<br>
adocommand.ActiveConnection = adoconnect<br>
adocommand.Execute
<br><br>
adocommand.CommandText = "Create temporary table old_pay SELECT min(old_pay)as old_pay, date  , account_card_header_id FROM account_card_previous_payment_due_dates where account_card_previous_payment_due_dates.old_pay >=  '" & Format(Options.DTPicker1, "YYYY-MM-DD") & "' group by account_card_header_id"<br>
adocommand.Execute<br><br>

adocommand.CommandText = "CREATE INDEX id_index ON  old_pay (account_card_header_id)"<br>
adocommand.Execute<br><br>

adocommand.CommandText = "DROP TABLE IF EXISTS old_type"<br>
adocommand.CommandType = adCmdText<br>
adocommand.ActiveConnection = adoconnect<br>
adocommand.Execute<br>
adocommand.CommandText = "Create temporary table old_type SELECT min(id) as id, type, account_card_header_id FROM account_card_type_change_log  where date_of_change > '" & Format(Options.DTPicker1, "YYYY-MM-DD") & "' group by account_card_header_id"<br><br>

adocommand.Execute<br>
<br>
adocommand.CommandText = "CREATE INDEX id_index ON old_type(account_card_header_id)"<br>
adocommand.Execute

        ' create phones<br>
        adocommand.CommandText = "DROP TABLE IF EXISTS NumbersForReports"<br>
        adocommand.CommandType = adCmdText<br>
        adocommand.ActiveConnection = adoconnect<br>
        adocommand.Execute<br>
        adocommand.CommandText = "Create temporary table NumbersForReports SELECT customer_id, group_concat(number_type, ' ', number, ' ', extension, ' || ' ) as number  FROM customer_phone_numbers where active = -1 group by customer_id"
        <br><br>
        adocommand.Execute<br>
<br><br>
        adocommand.CommandText = "CREATE INDEX id_index ON NumbersForReports(customer_id)"<br>
        adocommand.Execute<br>
<br><br>
        ' create addy<br>
        adocommand.CommandText = "DROP TABLE IF EXISTS AddyForReport"<br>
        adocommand.CommandType = adCmdText<br>
        adocommand.ActiveConnection = adoconnect<br>
        adocommand.Execute<br>
        adocommand.CommandText = "Create temporary table AddyForReport SELECT customer_id , group_concat(address_line_1, ' ', address_line_2, ' ', city, ' ', state, ' ', zip, ' || ') as addy  FROM customer_addresses where active = -1  group by customer_id order by customer_id,  default_bill_to"<br><br>
        
        adocommand.Execute<br>
        <br>
         adocommand.CommandText = "CREATE INDEX id_index ON AddyForReport(customer_id)"<br>
        adocommand.Execute<br>

henke37

You know you are doing something wrong if you have to add indexes to your temporary tables.

jlego

hahaha

pjt33

@henke37 said:

You know you are doing something wrong if you have to add indexes to your temporary tables.

Why would you use a temporary table if you didn't need an index? That's their primary benefit over table variables.

(FWIW I've written one SP which uses temp tables and gets a massive boost from having an index on them).

@henke37 said:

You know you are doing something wrong if you have to add indexes to your temporary tables.

Do you think that its possible that you haven't worked on large enough datasets to be qualified to make this statement?

jlego

your stearing away from the original post though

the point is one function creates like 10 temp tables for data that is already in regular tables, when printed out the entire function is 31 pages long. FOR one function. How do you troubleshoot something like this? In my five years of working with a large database I have created 1 temp table the entire time, because i needed it, not because i thought it was better but i had to. Alot of things I have read say 'stay away from temp tables unless you really need to use them.'

@jlego said:

your stearing away from the original post though
the point is one function creates like 10 temp tables for data that is already in regular tables, when printed out the entire function is 31 pages long. FOR one function. How do you troubleshoot something like this? In my five years of working with a large database I have created 1 temp table the entire time, because i needed it, not because i thought it was better but i had to. Alot of things I have read say 'stay away from temp tables unless you really need to use them.'

Obviously the original post was worthy of the exclamation WTF, I responded to the item about indices on temp tables. I find them quite useful. I never start out solving a problem with temporary tables, but frequently they are quite useful. With any query slightly more complicated than a simple select, your database will create a temporary table for you. I am quite sure that you make heavy use of temporary tables, without your knowledge.

Temporary tables allow you break up a large problem into easily managed pieces during development. During the performance tuning phase, I may remove the temporary tables or add indices. Temporary tables are your friend.

jlego

i know what you mean, but the function basically returns a sum or two columns with some customer information. I myself don't feel that is worthy of 10 temporary tables.. Don't get me wrong, im not saying don't use them - just don't over use them like the original function.

Jaime

@jlego said:

...Dim adoconnect As New ADODB.Connection...

You still maintain code that uses ADO? I feel for you.

sh_code

@jlego said:

How do you troubleshoot something like this?

F11 (Step into) is your friend

jlego

my plan is to rewrite most of it in vb.net, get it stable so i dont hear complaints all the time and then start from scratch with the entire system in c#

boomzilla

@jlego said:

my plan is to rewrite most of it in vb.net, get it stable so i dont hear complaints all the time and then start from scratch with the entire system in c#

Good call. Then you'll get the complaints all the time again.

jlego

yeah, that's when i go find another job myself