d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Sql Performance Question
Add Reply New Topic New Poll
Views: 166Replies: 4Track Topic
Member
Posts: 8,635
Joined: Dec 28 2007
Gold: 87.00
#1
Oct 24 2012 06:25am
I have tables with around 100 million rows and I have performance problems with ETL. I need to get these queries to run faster than they do now, here is example of one of the queries and the other ones are similar.

Code
SELECT *
FROM table1
WHERE EXISTS (
SELECT 1
FROM table2, table3
WHERE
table2.key=table1.key
AND table2.val IS NOT NULL
AND table3.key=table1.key2
AND table3.val IS NOT NULL
AND table3.timestamp >= TO_DATE('2012-10-01','YYYY-MM-DD')
)


Any suggestions to make this query faster?
Member
Posts: 5,641
Joined: Apr 13 2006
Gold: 2.00
#2
Oct 24 2012 06:42am
Code
SELECT 1 into #temptbl
FROM table2, table3
WHERE
table2.key=table1.key
AND table2.val IS NOT NULL
AND table3.key=table1.key2
AND table3.val IS NOT NULL
AND table3.timestamp >= TO_DATE('2012-10-01','YYYY-MM-DD')


Code
SELECT *
FROM table1
WHERE EXISTS (
select 1 from #temptbl)


RUn the subquery before the main query and put the results into a temp table then use the temp table in your select. This will cause your subquery to run 1 time and have all the wanted results. Then the main query just has to match against pre-set results instead of every row in the main table.
Member
Posts: 8,635
Joined: Dec 28 2007
Gold: 87.00
#3
Oct 24 2012 06:50am
Quote (SilverMice @ 24 Oct 2012 14:42)
Code
SELECT 1 into #temptbl
FROM table2, table3
WHERE
table2.key=table1.key
AND table2.val IS NOT NULL
AND table3.key=table1.key2
AND table3.val IS NOT NULL
AND table3.timestamp >= TO_DATE('2012-10-01','YYYY-MM-DD')


Code
SELECT *
FROM table1
WHERE EXISTS (
select 1 from #temptbl)


RUn the subquery before the main query and put the results into a temp table then use the temp table in your select. This will cause your subquery to run 1 time and have all the wanted results. Then the main query just has to match against pre-set results instead of every row in the main table.


Will this work if the data source and delivery table are in different databases (I only have read access to data source database)? Forgot to add it in the original post.
Member
Posts: 5,641
Joined: Apr 13 2006
Gold: 2.00
#4
Oct 24 2012 07:00am
By different databases do you mean you have 2 databases on SQL Server or you are linking between databases like Oracle and SQL Server?
Member
Posts: 8,635
Joined: Dec 28 2007
Gold: 87.00
#5
Oct 24 2012 08:22am
Quote (SilverMice @ 24 Oct 2012 15:00)
By different databases do you mean you have 2 databases on SQL Server or you are linking between databases like Oracle and SQL Server?


2 different database servers.
Go Back To Programming & Development Topic List
Add Reply New Topic New Poll

© 2003-2026 d2jsp  Contact