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.
| Author |
Topic |
|
dmitryv
Starting Member
1 Post |
 Posted - 2010-01-16 : 02:56:49
|
Hello everyone! I'm using Microsoft SQL Server 2008 SP1 with latest patches installed. I have found a problem recently: it runs lagre queries very bad. I have created a simple program in C# as a demo: <pre lang="C#">namespace Sandbox { public class Program { private static readonly StringBuilder _idsList = new StringBuilder("1000"); private static void Main(string[] args) { Console.WriteLine("Connecting..."); SqlConnection conn = new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Test;"); try { conn.Open(); Console.WriteLine("Connected OK. Testing..."); for (int i = 1; i < 1000000; i += 1000) { RunSql(conn, i); } Console.WriteLine("Done."); } catch(Exception ex) { Console.WriteLine("ERROR: " + ex); } } private static void RunSql(SqlConnection conn, int queryLength) { while (_idsList.Length < queryLength) { _idsList.Append(", 1000"); } Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); using (SqlCommand cmd = conn.CreateCommand()) { StringBuilder cmdText = new StringBuilder("declare @test table (id int) select * from @test where id in "); cmdText.AppendFormat("({0})", _idsList); cmd.CommandText = cmdText.ToString(); cmd.ExecuteScalar(); } stopWatch.Stop(); Console.WriteLine(queryLength + ": \t" + stopWatch.ElapsedMilliseconds + " ms"); } }</pre> This program is simple and straightforward: it generates the following SQL queries: <pre lang="SQL">declare @test table (id int) select * from @test where id in (1000, 1000, ...)</pre> Everything must be ok but what we see as output:  I have measured the performance from this program and made the plot (query length -> time), here's it:  I have discovered one interesting moment: When the query is large, it has last wait type <code>SOS_SCHEDULER_YIELD</code>. WHAT'S THIS???  Also, as you can see, it consumes too much cpu. If the server is undel heavy load, it can skip this query at all (in this case, we'll get a timeout in the calling code). Please help.. Any workaround as appreciated. Thanks in advance |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 04:38:34
|
| "select * from @test where id in (1000, 1000, ...)"If I read this right the @test table is empty?Using IN to find multiple, unrelated, values is only a reasonable approach when there are very few items in the IN clause (maybe 100 or so is OK, others may set that higher still, we don't here).So I'm not surprised for large values your IN(...) is performing badly.I don't suppose it makes a difference for an empty table, but you have no PK on the table, and using an @TableVar in-memory table won't be representative of how a query would operate using a real table. |
 |
|
|
|
|
|
|
|
Large queries problem