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.

I am using SQL Server 2008 Express edition and I've worked two days on a problem where seems to be a cross join in running a query.

I would want to know the set returned per join operation visually.

Is there any tool that can give you intermediate query results? I couldn't find one on Google.

EDIT

I solved this problem with the oldest trick in the book, manually running the query before joining to see where exactly the problem of cross join was happening. And found out that there was a mapping DB that sent 3 results for a join instead of just one (because it needed two conditions for a proper inner join, instead of one).

share|improve this question

1 Answer 1

up vote 2 down vote accepted

There are none that I know of that will show you the exact results - maintaining hooks so such tools could spy on what is going on would probably be inefficient in production (no doubt MS can do it in their development builds). While intermediate results aer sometimes dumped to temporary tables for further processing, they are often never materialised as a whole unit instead being streamed between parts of the query engine as they are produced.

If you run the query in SQL Server Management Studio, you can request that the query plan used is output. While that will not give you the entire intermediate results it will show you what comparisons are being performed and approximately how many rows are effectively output at each step - if you have a Cartesian product between a pair of large tables this should hopefully make it clear where in the process this happens.

For more specific help you'll need to add more detail to your question (please add it to the question, not as comments here, then there is better visibility of the new information for people who visit this page later):

  • What makes you think there is a cross join happening? Are you getting many more rows than you expect then whittling them down with a DISTINCT? If it is just that the query is running very slowly and spinning the CPU then it may instead be a index use problem or a correlated subquery that is resulting in a scan per output row (a situation that can actually be worse than a cross join performance wise)
  • Could you provide a copy of the query and relevant table structures?
  • What size of data are we talking about?
share|improve this answer
    
accepting because of paragraph 2 in your answer. –  Aniket May 7 '13 at 18:48
    
Of course you are far less likely to get a Cartesian product accidentally if you use explicit joins. –  HLGEM May 7 '13 at 19:41

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.