?
Solved

SQL Query issue

Posted on 2014-11-08
7
Medium Priority
?
177 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
  • 3
  • 3
7 Comments
 

Assisted Solution

by:camster123
camster123 earned 400 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 1600 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

601 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