Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query issue

Posted on 2014-11-08
7
Medium Priority
?
165 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 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

671 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