Tag Info

Hot answers tagged

9

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows. Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from. If ...


9

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision). Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch. I was thinking about this but we don't really need to ...


4

So it's an indexed view - SQL Server can automatically choose to use an indexed view if the base table is referenced and the indexed view can (better) satisfy the query. From MSDN (emphasis mine): A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains ...


4

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time? Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time. By centralising some simple logic it appears I impede performance through code reuse. Yes, for scalar valued functions that is true. ...


3

Non-clustered indexes use the clustered index key value as a pointer back to the data. If you create a compound clustered index, you are making your non-clustered indexes that much larger. When choosing a clustered index, especially on tables with hundreds of millions of rows, try to choose a single narrow column if possible. i.e. choose an integer over a ...


3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time: CREATE TABLE measurements ( id bigint IDENTITY, parameter_id int NOT NULL, measuretime datetime NOT NULL, value float NOT ...


2

EXPLAIN shows an execution plan for SELECT queries. In version 5.6, it was extended to include plans for INSERT, UPDATE, DELETE and REPLACE statements. No version of MySQL (not even the still in development, 5.7) has EXPLAIN for CREATE TABLE or CREATE VIEW. You can of course run the explain for the select (that defines the view): EXPLAIN EXTENDED ...


2

There are multiple design changes to make. Use InnoDB, not MyISAM. You need row-level locking, not table-level locking. (And set innodb_flush_log_at_trx_commit = 2) A blog on conversion tips: http://mysql.rjweb.org/doc.php/myisam2innodb . A MyISAM INSERT always blocks a SELECT. An InnoDB INSERT rarely blocks a SELECT. Rethink your counters. You have ...


2

The way that your query is running is this; Pull rows from tblUserAction starting with @LastRowID+1 and see if that row matches the right @ClientID. Keep going until you've found 10,000 of them This works well if you don't have far to look, but is lousy for those clients with relatively few records, when it would be more useful to find all the records for ...


2

There is a 3rd option, using window functions, either max(): with latest as (select *, max(inserted_day) over (partition by hostname) as max_inserted_day from age_data ) select * from latest l where inserted_day = max_inserted_day ; or rank(): with latest as (select *, rank() over (partition by hostname order ...


2

You can use this select st.text, qp.query_plan, qs.plan_handle, total_worker_time/execution_count AS [Avg CPU Time] FROM sys.dm_exec_query_stats AS qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st cross apply sys.dm_exec_query_plan(qs.plan_handle) qp if you are using sql server 2008 and above you can configure Extended Events as ...


1

No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index. One thing seems worth mentioning: Updates on columns involved in an index in any ...


1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. ...


1

In your case, your problem is RAM. Why? In my old post What are the main differences between InnoDB and MyISAM?, I discuss how each Storage Engine does caching. InnoDB has a Buffer Pool that cache data and index pages MyISAM does no dedicated caching of data, only indexes. Data get cached in OS RAM. Here is a picture from Vadim Tkechenko that shows what ...


1

Here's the difference between the two statements: The first statement calls the levenshtein function only on the retrieved resultset. The second statement calls the levenshtein and the length function as a so called "predicate" for every row in the table to retrieve the resultset. Even if levenshtein is only evaluated if the first condition is true, then ...


1

All columns on both tables are indexed. Enough said. That is not the right way to go about indexing. WHERE db2.tb2.c1 = db1.tb1.c1 db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1). and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N' ...


1

You should try to remove the join to tblClient, since it does not seem that you use any data from that table. Also, if there is not already an index on ClientId in tblCustomer, you could see if adding it will speed up the query. If you are able to, I would also follow the advice of Max Vernon, and change the primary key to just be on the IDENTITY column. ...



Only top voted, non community-wiki answers of a minimum length are eligible