How can I make this query faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I make this query faster?

Author

Topic

josephap
Starting Member

1 Post

Posted - 2010-08-26 : 13:29:42

Hello everyone,

I just began using SQL last week. I am using an sqlite database in a piece of software to parse data for a research project.

The following query is something that I need to run several thousand time, but it takes 40 seconds or more to run each time. I am sure there is a ways to make it much much fast, except I have no idea where to begin. Interestingly, most of the time is the result of the query involving the outermost IN clause, everything within the outermost IN statement takes only 5 seconds on my machine. Any suggestions?

SELECT COUNT (patent) FROM (
SELECT patent,gyear FROM patsic06_mar09_ipc WHERE patent IN (
SELECT citing FROM cite76_06 WHERE cited IN (
SELECT patent FROM (
SELECT * FROM patsic06_mar09_ipc WHERE pdpass IN (
SELECT pdpass FROM dynass WHERE pdpco1 IN (
SELECT pdpco FROM pdpcohdr WHERE cusip='"881694"'
) OR pdpco2 IN (
SELECT pdpco FROM pdpcohdr WHERE cusip='"881694"'
) OR pdpco3 IN (
SELECT pdpco FROM pdpcohdr WHERE cusip='"881694"'
) OR pdpco4 IN (
SELECT pdpco FROM pdpcohdr WHERE cusip='"881694"'
) OR pdpco5 IN (
SELECT pdpco FROM pdpcohdr WHERE cusip='"881694"'
)
)
) WHERE gyear<=1996
)
)
) WHERE gyear>=1996 AND gyear<=2001

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 15:33:18

This is your query, just reformatted.
See something that affects performance?

SELECT	COUNT(patent)
FROM	(
		SELECT	patent,
			gyear
		FROM	patsic06_mar09_ipc
		WHERE	patent IN	(
						SELECT	citing
						FROM	cite76_06
						WHERE	cited IN	(
										SELECT	patent
										FROM	(
												SELECT	*
												FROM	patsic06_mar09_ipc
												WHERE	pdpass IN	(
																SELECT	pdpass
																FROM	dynass
																WHERE	pdpco1 IN	(
																				SELECT	pdpco
																				FROM	pdpcohdr
																				WHERE	cusip = '"881694"'
																			)
																	OR pdpco2 IN	(
																				SELECT	pdpco
																				FROM	pdpcohdr
																				WHERE	cusip = '"881694"'
																			)
																	OR pdpco3 IN	(
																				SELECT	pdpco
																				FROM	pdpcohdr
																				WHERE	cusip = '"881694"'
																			)
																	OR pdpco4 IN	(
																				SELECT	pdpco
																				FROM	pdpcohdr
																				WHERE	cusip = '"881694"'
																			)
																	OR pdpco5 IN	(
																				SELECT	pdpco
																				FROM	pdpcohdr
																				WHERE	cusip = '"881694"'
																			)
															)
											)
										WHERE	gyear <= 1996
									) 
					)
	)
WHERE	gyear >= 1996
	AND gyear <= 2001

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 15:41:02

Run this query and see the result. Report back the number of records it produces.

SELECT		x.patent
FROM		patsic06_mar09_ipc AS x
INNER JOIN	dynass AS z ON z.pdpass = x.pdpass
INNER JOIN	pdpcohdr AS y ON y.pdpco IN (z.pdpco1, z.pdpco2, z.pdpco3, z.pdpco4, z.pdpco5)
			AND y.cusip = '"881694"'
INNER JOIN	cite76_06 AS t ON t.cited = x.patent
GROUP BY	x.patent
HAVING		MAX(CASE WHEN x.gyear <= 1996 THEN 1 ELSE 0 END) = 1
		AND MAX(CASE WHEN x.gyear >= 1996 AND x.gyear <= 2001 THEN 1 ELSE 0 END) = 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources