SQL Query issue

Hi

I am trying to join two tables MARA_MBEW (2 million records) and MARC (70000 records) using the SQL code below, but the result seems so large that SQL Server Management Studio keeps on running without a result. I have created to joins. What is wrong with my SQL syntax?

I get the following errors":
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage:  143338021322752' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


SELECT b.[Article] As [MARC | Article], b.[Site] As [MARC | Site], a.[Article description] As [MARA_MBEW | Article description], a.[BUn] As [MARA_MBEW | BUn], a.[MS] As [MARA_MBEW | MS], a.[Total Stock] As [MARA_MBEW | Total Stock], b.[MS] As [MARC | MS], b.[MRPC] As [MARC | MRPC], b.[Typ] As [MARC | Typ] FROM [MARA_MBEW] a INNER JOIN [MARC] b ON (a.[Article] = b.[Article]),[MARA_MBEW] c INNER JOIN [MARC] d ON (c.[ValA] = d.[Site])
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

camster123Senior C++ Software EngineerCommented:
murbro,
       I was wondering whether you used clustered indexes for SQL Server Managment Studio.  The clustered b-tree index is required for inner joins with 2 million rows. Thank you. FrankC123
0
PortletPaulfreelancerCommented:
NEVER mix "old fashioned join syntax" and ANSI syntax together, and this is an excellent example of why you should not do it.

You have a MASSIVE Cartesian product being produced by that query because you have not specified a join for this:   , [MARA_MBEW] C so a Cartesian product results.
FROM [MARA_MBEW] A
     INNER JOIN [MARC] B ON (A.[Article] = B.[Article])
   , [MARA_MBEW] C
     INNER JOIN [MARC] D ON (C.[ValA] = D.[Site])

Open in new window

If you avoid old fashioned syntax this cannot happen because you have to specify CROSS JOIN when you want a Cartesian product.

I don't see why you specify [MARA_MBEW] twice, why not just join [MARC] twice using the different join conditions. Try it this way:
FROM [MARA_MBEW] A
     INNER JOIN [MARC] B ON A.[Article] = B.[Article]
     INNER JOIN [MARC] D ON A.[ValA] = D.[Site]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

camster123Senior C++ Software EngineerCommented:
PortletPaul,
       Please explain to me why you did not use b-tree indexes in your answer. Thank you. frankc123
0
PortletPaulfreelancerCommented:
@frankc123

I made no mention of indexes, this issue as I saw it, was purely syntax.
0
camster123Senior C++ Software EngineerCommented:
Mr. @PorterPaul,
      I was taught at Oracle Training in Boston, MA right next to the Copley Place that if you want to join two tables, one of which has  2 million rows that an clustered B-Tree index might speed up query execution time.
      May I ask if you have run EXPLAIN PLAN?
       Thank you, frankc123
0
PortletPaulfreelancerCommented:
@frankc123

This question is in the MS SQL Server Topic. But to answer your question: No I have not run an explain plan as I do not have any of the tables
 (ps: in SQL Server they are called Execution Plans)

Without doubt indexing makes a big difference to query performance but that issue isn't relevant to this question. In SQL Server the choice for indexes is clustered or non-clustered indexes, so I just don't believe there is more I can add.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.