Take the 2-minute tour ×
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.
  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

migration rejected from stackoverflow.com Jun 2 at 5:51

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Max Vernon, ypercube, Paul White, RolandoMySQLDBA, Michael Green Jun 2 at 5:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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 '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 '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 '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 –  Frisbee Mar 13 '13 at 22:23
1  
Could you provide CREATE TABLE scripts and query plan outputs to help illustrate what you are trying to achieve here please? –  Thomas Kejser Jan 5 '14 at 17:08