HELP!!!...JOIN Problem 
Author Message
 HELP!!!...JOIN Problem

Hi everybody!!!
I hope somebody can help me with the following problem:
I'm programming a stored procedure in SQLServer 6.5 (Windows 95), as a
matter of fact it works but with high amount of information it's extremely
slow and/or it breaks the connection with the server, in this test I have a
15 fields temporary table called 'Table1' with 6869 records and a 5 fields
temporary table called 'Table2' with 42681 records, I need to make a join
with these tables and insert results in another table, but without the
insert, just making the select (also outside the stored procedure) I
continued having problems, what I did while testing outside the stored
procedure was to create a clustered index in 'Table2' and it worked much
better, it was really much faster and it didn't break the connection, this
happened even I forzed the index or not, so, I replaced this in the stored
procedure, but it worked as if the index didn't exist, so I forzed it, and
when I executed the stored procedure, an error said that the index didn't
exist, I did a select from tempdb..sysindexes where name like '%table2%'
and the index appeared, but it wasn't recognized inside the stored
procedure. This happens also if I create the tables as not temporary ones.
I'm really trying to do a simple join similar to the following:

SELECT *
FROM Table1 t1, Table2 t2
WHERE t1.Id = t2.Id

What can I do???, could you help me????, please :), I'll really appreciatte
your help.

        Thanks in advance :)

Brenda Ivett



Mon, 01 Jan 2001 03:00:00 GMT
 HELP!!!...JOIN Problem

For a statement inside the stored procedure to use an index created in the
stored procedure, you'll need to run the statement with EXEC because the
query plan for the stored proc doesn't take the index into account.  Here is
an example:

create proc test_index
as
select * into #tempauth from authors
create index tmpindx on #tempauth (au_id)
exec ('select * from #tempauth where au_id = ''047-43-0360''')
go

--
Bob Pfeiff
MCSD, SQL Server MVP
Spectrum Technology Group, Inc.



Mon, 01 Jan 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. HELP!!!!...JOIN Problem

2. Problem with Outer join and Inner joins

3. Inner Join / Self Joins help!

4. Help on converting old style JOIN to ANSI JOIN

5. Join Problem, Help???

6. Help with LEFT JOIN problem

7. Complex join problem with lookup table - need Help

8. Need help please: Problem with outer join

9. Need help w/ query - OUTER JOIN problem

10. Help: INNER JOIN problem, any SQL-Gurus ?

11. Joining problems - please help

12. Help: Another INNER JOIN problem!


 
Powered by phpBB® Forum Software