Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

  1. I use a table insert to UNION a CTE of converting transactions to visits (converting or not) and then summarize the visit data (no details, just pages visited & txns completed) to get funnel clickthrough & overall conversion; after this, RIGHT JOIN to the VisitData table

    • The transaction table has 500k relevant rows or less for every run of this process, but
    • The VisitData table has 3MM or more rows per day, so if I run this weekly it ends up in a 25MM-row batch.

  2. To get overall visit info for converting and non-converting visits into one summary table, as mentioned above, I have to:

    1. RIGHT JOIN the reasonably-sized Transactions CTE_T, 500k of ~2MM Txn table rows, to
    2. the Ridicularge Visits table V, ~90MM rows per partition
      • Since the Visits table is so huge, it is partitioned by date, and although it has indices for the keys I join on, SQL Server still does a table scan to attach the rest of the augmenting data (e.g. visitor location etc.) from the visit table V to the transactions info from CTE_T.

This takes a very long time, between 3-7 hours depending on how wide the timespan is (the Visit table is partitioned by month @ 90MM rows per partition), but never less than 2 hours JUST TO SCAN THE TABLE (!) before any query/join logic is applied or any other operation performed.

  • My question is this: is it possible to write a table-valued function that will return (@UserID,VisitData1,VisitData2,etc), allowing SQL Server to spool (I'm told) this in the background while allowing other things to occur in the meantime?
  • If the answer is YES, have you experienced significant (or even just noticeable) gains in performance using this method?
share|improve this question
1  
You describe various partitions, but are all these partitions on separate spindles with separate read/write arms and separate controllers? – Pieter Geerkens Mar 13 at 21:34
@PieterGeerkens: I know that they are spread across two physical drives; I'll find out what logic determines which partition goes where, & if there is further physical operational logic or not. Thanks for clarifying. – 223fms Mar 13 at 21:48
1  
@223fms: Also check what else is on those drives: indices, logging, etc. You are undoubtedly I/O bound, and every movement of the disk not related to your query is slowing you down. – Pieter Geerkens Mar 13 at 21:53
1  
CTE is just syntax. It get evaluated. Is that CTE_T, 500k of ~2MM evaluated multiple times? I think you need post the TSQL – Blam Mar 13 at 22:23

migrated from stackoverflow.com Mar 13 at 23:19

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.