January 21, 2014 at 5:08 pm
Hello, I wanted to get some feedback on basic query performance in SQL Server for the following scenario:
* table has 10 columns
* I want to query table on 2 columns which are guids
* table will have 1 matching row
* table may have up to 100k rows
Is it safe to say that the query should return within 1 second? For this scenario, the SS hardware, network configuration, etc is good.
January 21, 2014 at 5:29 pm
It depends.
What about possible network issues? Wil the query use functions? indexes?
Many things can cause poor performance or great performance.
January 21, 2014 at 5:37 pm
select userid, incidentid from mytable
no functions or indexes. running query through ssms directly on the db server
January 21, 2014 at 5:48 pm
It still depends on the time needed to display the results, possible table locks, disk speed, etc
January 21, 2014 at 5:50 pm
If you expect only one row as result then you should create an index on the column participating in the WHERE clause in order to improve performance.
Indexes in a table, especially in such cases where only few rows are expected, can significantly improve performance and the execution time for your query should be significantly under 1 sec (in the case you described).
___________________________
Do Not Optimize for Exceptions!
January 22, 2014 at 4:03 am
I think the point made stands. There are too many things that can affect the performance of a query for good or bad, even a simple query.
Best to test the query in question and if it doesn't return results in a timely manner then you can go about finding out why.
January 22, 2014 at 6:48 am
While I don't argue with everyone's caveats, let's focus just a bit. If we're talking about a simple query:
SELECT a.*
FROM MyTable AS a
WHERE a.GuidColumn = 'Appropriate value';
And that column is a clustered index (and yes, we can argue the merits of clustering a GUID), on a VM on my laptop with 4gb of memory, this query returns in, one heck of a lot, faster than 1 second.
As a generic question then, your answer would be yes. But, there are a lot of other considerations that go into understanding query performance, as was noted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2014 at 11:37 am
You'll want indexes on the lookup guids. And, since they're guids, you'll need to rebuild the indexes fairly frequently. You should also strongly consider reducing the FILLFACTOR on those indexes to reduce page splits between rebuilds. Reduced FILLFACTOR won't affect the speed of a single-value lookup, but could help increase INSERT speed to the main table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply