Tag Info

Hot answers tagged

7

This may not directly answer your question, however you have multiple potential issues in your query, that are immediately visible to me. AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31' AND DateDiff(day,Valor,GetDate()) > 15 Why are you doing CONVERT(DateTime, Valor) ? Is Valor not a DateTime type in the table? If not, you're doing ...


5

This should do what you need SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta Or you could use the following SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


4

Why do you use this view at all for this query? The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint ...


3

Could I through indexing alone influence SQL server to run this much faster. Possibly. There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the ...


3

Ignore the "fragmented" alarm; it's bogus. Ignore the max "possible" memory; it's bogus. Focus on the slow queries; they are causing the high CPU. What are the slow queries? Plan A: Glance at SHOW FULL PROCESSLIST; frequently. You will see one or two queries there most of the time. Plan B: Turn on the slowlog; set long_query_time = 2; and (after a ...


3

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...


2

QUERY #1 Each time you do an INSERT, you are doing this under the hood SET @sql = 'insert t select null'; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times. QUERY #2 Running insert t select null from(, you fully ...


2

If the table is InnoDB, here is what's happening: The query optimizer sees SELECT * and does this Sees all columns are included in the SELECT list Uses the clustered index since all columns are included The query optimizer sees SELECT batch_id, study_id and does this Sees SELECT list has two columns, not all columns Sees the study_id index (and other ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

In situations where one static sql (procedure) executes better than an equivalent one, I would examine: a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case. b) are the ...


1

I would suggest normalizing data and splitting the table into the following two tables: CREATE TABLE `file_data` ( `data_id` bigint(20) NOT NULL AUTO_INCREMENT, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', PRIMARY KEY (`data_id`), UNIQUE (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `file_names` ( ...


1

You can try selecting into a temp table with the zip criteria only, and then select from the temp table with the additional criteria. SELECT * INTO #MyTemp FROM (SELECT * FROM MyTable where zip='1234') data SELECT * from #MyTemp where other = 'Y' DROP Table #MyTemp Not sure if this will be faster, but since the zip-only query runs fast, then you're just ...


1

Is there a better way to write this type of query? No, the way you've written it is the "best" way. Unless, of course, that way doesn't work. The joy and frustration of using a declarative language is the optimiser. It is your best friend when it works and worst enemy when it doesn't. One way to kick the optimiser into doing the right thing is to ...


1

The following is probably an issue: [!!] Maximum possible memory usage: 13.4G (347% of installed RAM) Due to limited memory, the database is probably using the swap/page file a lot, as well as significant disk i/o through out. This might cause CPU utilization to remain rather high at all times.


1

I don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic. Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above. I'd be interested in seeing what the reaction was of the person ...


1

It really depends. First of all I'm assuming you are using SQL Server although I believe all RDBMSes will work about the same in this respect. There are two major options: If there is no index that the optimizer can use then it will do a table scan. In this particular case it will run through every row (all 8400) and look for cases where name = 'jhon'. ...


1

Again, I'm a bit unclear. If you run the SELECT in your question, and if the users field is indexed, then it will to go straight to and then stop after it finds jhon. If there are multiple jhons, it will return those and then stop, the index being used to determine the point at which the server will stop searching. If the field isn't indexed, then it will ...


1

I have a simpler way to put it. "Batched INSERTs and LOAD DATA run 10 times as fast as single-row INSERTs." By "batching", I mean INSERT INTO t (a,b) VALUES (1,2), (2,3), .... The optimal number is between 100 and 1000 rows per INSERT. Beyond that, you get into diminishing returns. Here are some issues that impact performance, especially for Batched ...


1

You can improve efficiency of your query by (for example) replacing: ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct1 ,( SELECT /*+ NO_MERGE */ ...



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