Solved

SQL Query issue

Posted on 2014-11-08
7
160 Views
Last Modified: 2014-11-13
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])
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Assisted Solution

by:camster123
camster123 earned 100 total points
ID: 40430958
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 40430962
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
 

Author Closing Comment

by:Murray Brown
ID: 40430967
Thanks very much
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:camster123
ID: 40439138
PortletPaul,
       Please explain to me why you did not use b-tree indexes in your answer. Thank you. frankc123
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40439180
@frankc123

I made no mention of indexes, this issue as I saw it, was purely syntax.
0
 

Expert Comment

by:camster123
ID: 40439338
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40439356
@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

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question