Solved

SQL Query issue

Posted on 2014-11-08
7
152 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:murbro
  • 3
  • 3
7 Comments
 

Assisted Solution

by:camster123
camster123 earned 100 total points
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 400 total points
Comment Utility
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:murbro
Comment Utility
Thanks very much
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Expert Comment

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

Expert Comment

by:PortletPaul
Comment Utility
@frankc123

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

Expert Comment

by:camster123
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
@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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now