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|improve this question

migrated from stackoverflow.com Mar 13 '13 at 23:19

This question came from our site for professional and enthusiast programmers.

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
@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
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 '13 at 22:23
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

Your Answer


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