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])
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])
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PortletPaul,
Please explain to me why you did not use b-tree indexes in your answer. Thank you. frankc123
Please explain to me why you did not use b-tree indexes in your answer. Thank you. frankc123
@frankc123
I made no mention of indexes, this issue as I saw it, was purely syntax.
I made no mention of indexes, this issue as I saw it, was purely syntax.
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
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
@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.
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.
ASKER