January 29, 2012 at 10:34 am
I have a db that is not setup in the best and most effecient way. What is the best way to query the tables below that will give me the best performance ?
The data is stored in the table (propval) and each column is stored in a table as a record. Tocid represents a collection of columns that creates 1 record. Prop_id represents the id of a column. Str_val is the value stored in the column if it is a string, short_str_val is same as str_val, num_val is the value stored in the column if it is a numeric value, if it is a date it is stored in the date_val column. Tocid represents 1 record for a collection of prop_id's
This is
tocidprop_idposstr_valshort_str_valnum_valdate_valbin_val
61931000%(GetBanner_Client Name)%(GetBanner_Client Name)NULLNULLNULL
16482130United StatesUnited StatesNULLNULLNULL
1648216020082008NULLNULLNULL
16482170127314127314NULLNULLNULL
16482180NULLNULL1NULLNULL
164821901137910011379100NULLNULLNULL
16482200COOPER TIRE AND RUBBER CO*COOPER TIRE AND RUBBER CO*NULLNULLNULL
16482820NULLNULLNULL7/16/2010NULL
16482830quickfieldsquickfieldsNULLNULLNULL
164821610NULLNULLNULL7/16/2010NULL
164821620NULLNULLNULL7/16/2010NULL
The Propdef table has the info about columns or data dictionary:
prop_idprop_nameprop_typeprop_lenprop_format
13Region L 130
18Vendor Rank I 4040
20Vendor Name S 400
132Net Claim Amount N NULL41
79PRGX Approval Date DNULL10
80Vendor Approved L 30
January 30, 2012 at 6:11 am
This is a sample of a query and data results. I think i may need to do a pivot but I've never created one....
Query:
SELECT top 100 tocid, prop_id, str_val AS 'Region', str_val AS 'Client_Number',str_val AS 'Client_Name', str_val AS 'Audit_Year', str_val AS 'Audit_Number'
,num_val AS 'Vendor_Rank', str_val AS 'Vendor_Number', str_val AS 'Vendor_Name', str_val AS 'Document_Type', str_val AS 'Owner'
,str_val AS 'Document_Security', str_val AS 'Auditor_ID', str_val AS 'Audit_Type', str_val AS 'Client_Division', num_val AS 'GST'
,num_val AS 'HST', num_val AS 'QST', str_val AS 'Cancel_Reason', str_val AS 'Language', str_val AS 'Creator', str_val AS 'Claim_Number'
,str_val AS 'Claim_Key', num_val AS 'Potential_Claim_Amount', num_val AS 'Claim_Amount', str_val AS 'Approval_Workflow', num_val AS 'Current_Status_Days'
,str_val AS 'PRGX_Approved', date_val AS 'PRGX_Approval_Date', str_val AS 'Vendor_Approved', date_val AS 'Vendor_Approval_Date'
,date_val AS 'Last_Updated_Date', str_val AS 'Last_Updated_User', str_val AS 'Claim_Cause_Description', str_val AS 'Source_Document'
,str_val AS 'Invoice_Number', date_val AS 'Invoice_Date', str_val AS 'Client_Claim_Number', str_val AS 'Client_Banner'
,str_val AS 'Client_Department', str_val AS 'Client_Category', str_val AS 'Claim_Currency', str_val AS 'Billable_Currency'
,num_val AS 'Exchange_Rate', str_val AS 'Original_Buyer', str_val AS 'Current_Buyer', str_val AS 'Vendor_Type', str_val AS 'Business_Unit'
,date_val AS 'Date_Entered', date_val AS 'Vendor_Available_Date', date_val AS 'Client_Available_Date', date_val AS 'Posting_Date'
,str_val AS 'Voucher_Batch_Number', num_val AS 'Claim_Age', str_val AS 'PRGX_Claim_Code', str_val AS 'PRGX_Claim_Description'
,num_val AS 'Net_Claim_Amount', str_val AS 'Status', date_val AS 'Deduct_Deadline', str_val AS 'Dup_Report', str_val AS 'Check_Number'
,date_val AS 'Check_Date', str_val AS 'Corporate_Vendor_Number', str_val AS 'Corporate_Vendor_Name', str_val AS 'Client_Approved'
,date_val AS 'Client_Approval_Date', str_val AS 'Client_Approval_Status', str_val AS 'Vendor_Approval_Status', str_val AS 'Vendor_Submission_Status'
,str_val AS 'Autodeduct_Status', str_val AS 'Last_Owner', str_val AS 'Incoming_Fax_Number', str_val AS 'PRGX_Approver', str_val AS 'Chaser'
,str_val AS 'Tracker', str_val AS 'Client_Contact_Name', str_val AS 'Vendor_Contact_Name', date_val AS 'Client_Resolved_Date'
,date_val AS 'Vendor_Resolved_Date', date_val AS 'Posting_Submission_Date', str_val AS 'Source_File', num_val AS 'Deduct_Deadline_Days'
,str_val AS 'Status_Description', str_val AS 'Client_Store', date_val AS 'Last_Adjustment_Date', str_val AS 'Auditor_Name'
,date_val AS 'Original_Client_Correspondence_Date', date_val AS 'Last_Client_Correspondence_Date', num_val AS 'Days_Since_Client_Correspondence'
,date_val AS 'Original_Vendor_Correspondence_Date', date_val AS 'Last_Vendor_Correspondence_Date', num_val AS 'Days_Since_Vendor_Correspondence'
,str_val AS 'PRGX_Claim_Category', str_val AS 'PRGX_Claim_Type', str_val AS 'PRGX_Approved_By', str_val AS 'Client_Approved_By'
,str_val AS 'Vendor_Approved_By', num_val AS 'Allowance_Percent', str_val AS 'Dup_Group_Number', str_val AS 'Dup_Payment_Invoice_No'
,str_val AS 'Buyer_Title', str_val AS 'Price_Protection_Item_Number', date_val AS 'Price_Protection_Date', str_val AS 'Foreign_Currency_Code'
,str_val AS 'Batch_Number', str_val AS 'VndNbrAlt', str_val AS 'Project_Code', date_val AS 'Resubmit_Date', num_val AS 'JV1_Percent'
,num_val AS 'JV2_Percent', num_val AS 'JV3_Percent', str_val AS 'JV_Store_1', str_val AS 'JV_Store_2', str_val AS 'JV_Store_3'
,num_val AS 'Client_Approval_Number', str_val AS 'Item_Description', str_val AS 'UPC_Number', num_val AS 'Net_Billable_Claim_Amount'
,num_val AS 'AuditTrax_RecNo', str_val AS 'Buyer_Code', str_val AS 'Buyer_Category', str_val AS 'Client_Account_Number'
,str_val AS 'Client_Controller', str_val AS 'Controller_Reviewed', date_val AS 'Printed_Date', num_val AS 'JV4_Percent'
,str_val AS 'JV_Store_4', str_val AS 'Account_1', str_val AS 'Account_2', str_val AS 'Account_3', str_val AS 'Account_4'
,str_val AS 'Client_Submission_Status', str_val AS 'Deduct_Deadline_Running', num_val AS 'Other_Adjustment_Total'
,num_val AS 'Cancellation_Total', num_val AS 'Payback_Total', str_val AS 'Allowance_Calculation_Basis'
,str_val AS 'Allowance_Calculation_Method', str_val AS 'Audit_Name', date_val AS 'Batch_Date', date_val AS 'Deduct_Date'
,str_val AS 'Industry', str_val AS 'Claim_Cause_Category', str_val AS 'Claim_Cause_Code', str_val AS 'Claim_Cause_Fault'
,date_val AS 'Voucher_Date', str_val AS 'Sub_Code', str_val AS 'Sub_Code_Description', str_val AS 'Line_Number'
,str_val AS 'Vendor_Contract_Number', str_val AS 'Additional_Vendor_Contract', str_val AS 'Client_Group'
,str_val AS 'Client_Program', num_val AS 'Price_Protection_Old_Cost', num_val AS 'Price_Protection_New_Cost'
,str_val AS 'Client_Delivery_Method', str_val AS 'Vendor_Delivery_Method', str_val AS 'Client_Department_Name'
,str_val AS 'Payback_Document_Number', str_val AS 'Client_Claim_Cause_Code', str_val AS 'Client_Claim_Cause_Description'
,str_val AS 'Client_Claim_Cause_Fault', str_val AS 'Business_Unit_Name', str_val AS 'Test_Field_List', date_val AS 'Payback_Check_Date'
,str_val AS 'Payback_Check_Number', str_val AS 'Payback_Invoice_Number', str_val AS 'Client_Division_Name'
,str_val AS 'Client_Category_Name', str_val AS 'Client_Group_Name'
FROM propval WITH (NOLOCK)
WHERE prop_id IN('13','14','15','16','17','18','19','20','21','22','29','30','31','32','33','34','35','38','39'
,'40','68','69','71','72','76','77','78','79','80','81','82','83','94','96','97','98','99','100'
,'101','102','103','104','105','108','109','110','111','112','113','114','122','124','127','128'
,'129','130','131','132','133','134','136','139','141','142','143','144','145','147','148','149'
,'150','157','165','173','174','175','176','177','178','179','180','182','205','206','207','213'
,'256','257','258','259','260','261','262','360','361','372','373','374','387','388','389','390'
,'392','393','396','398','410','416','417','425','426','427','428','429','430','441','487','488'
,'490','491','492','494','495','499','500','502','503','504','505','506','507','508','509','512'
,'514','515','516','517','518','519','520','521','522','523','524','525','526','527','528','529'
,'530','531','532','533','534','544','545','548','550','552','554','555','556','560','561','562'
,'564','565','566'
)
(There are more rows and columns but too many to display..but I think you'll get the picture)
Results:
tocidprop_idRegionClient_NumberClient_NameAudit_Year
1059786104USDUSDUSDUSD
1059786105NULLNULLNULLNULL
1059786108NULLNULLNULLNULL
1059786109BuzinkyBuzinkyBuzinkyBuzinky
1059786110Domestic OnlyDomestic OnlyDomestic OnlyDomestic Only
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply