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.
To get overall visit info for converting and non-converting visits into one summary table, as mentioned above, I have to:
- RIGHT JOIN the reasonably-sized Transactions CTE_T, 500k of ~2MM Txn table rows, to
- 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?