Slow query - Bad design or implementation

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

SQL Server 2000 Forums

SQL Server Development (2000)

Slow query - Bad design or implementation

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 01:53:09

I have a CVS file that I am running a query against which works, but is getting very slow, but I don't know if it's because the query is bad or how I am performing the query which is killing it (I am using ADO.NET to perform the query straight against the CVS file).

Now I know that if this was in a database, it would be faster, but can I make some improvements to my query so I don't have to put all the data in a database first?

The query looks as follows:


SELECT DISTINCT Originator, COUNT(*) 
FROM [log_report.csv] f1 
WHERE [date] < #1/12/2003# 
AND (
	SELECT COUNT(*) 
	FROM [log_report.csv] f2 
	WHERE f1.Originator = f2.Originator 
	AND LCASE(f2.Content) = 'stop' 
	AND NOT EXISTS(
		SELECT * 
		FROM [log_report.csv] f3 
		WHERE f2.Originator = f3.Originator 
		AND f3.Date > f2.Date)
	) = 0 
GROUP BY Originator 
HAVING COUNT(*) > 3 
ORDER BY COUNT(*) DESC

The file is basically a list of mobile phone numbers (Originator) that have sent a SMS (Content) after a certain date. They should have sent at least 3 messages, and their last message can't be 'stop'.

Any ideas on making the query faster? Or should I just dump the data into a database first then run the query.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-01 : 02:18:05

Immediate thought is that this will be faster (changed the < to >)

SELECT DISTINCT Originator, COUNT(*)
FROM [log_report.csv] f1
WHERE [date] > #1/12/2003#
AND 'stop' <> (
select top 1 LCASE(f2.Content)
FROM [log_report.csv] f2
WHERE f1.Originator = f2.Originator
WHERE f2.[date] > #1/12/2003#
order by f2.[date] desc
)
GROUP BY Originator
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 20:20:18

Thanks nr, that's a much better query but it's still quite slow so I am going to dump the data into a database then run the query. I'll let you know how the performance changes.

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-12-03 : 01:16:01

Well I changed this to a database and it is a heap faster, and I am just using an access database. Thanks for your help on this query nr, thats a much nicer solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources