Tag Info

Hot answers tagged

22

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article: ...


12

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)). I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it) DROP TABLE dbo.Test CREATE TABLE dbo.Test ( c1 VARCHAR( ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


4

For the specific query: select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56' ; -- use single quotes, not double an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index: ALTER TABLE writetest_table ADD INDEX ...


3

You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether ...


3

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql ...


3

The index that will give you the most benefit is one on fldB, fldC, fldA desc or on fldC, fldB, fldA desc depending on your data. You'll need to check your data and keys to determine which column should go first. The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table ...


3

SELECT id FROM accesses WHERE token IS NOT NULL; The perfect index for this specific query would be a partial index: CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL; The index condition is the important part. On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the ...


3

Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements. Wrong memory configuration As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of ...


2

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use... and Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use? This is partially true. Index fragmentation wont cause HIGH CPU. Internal ...


2

I have studied your question hard, but can't make sense of the procedure you describe. (You might work on the description some more.) Why would you generate sequence numbers by hand, when you can just have Postgres generate them automatically? Per documentation: If a list of columns is specified, COPY will only copy the data in the specified columns ...


2

Using the MONTH() function on a column automatically disqualifies any index from usage You can state the range in the WHERE clause SELECT s.id, s.player, COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin, COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when ...


2

Memory related settings You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file. Actually, there's a litany of good info in this ...


2

Looks like my short answer was not to everyone's taste, so let me show you the long answer First of all, let's create our lab. @WestFarmer mentioned a couple of tables SQL>create table A ( 2 some_column number, 3 another_column number 4 ); Table created. SQL>create table B ( 2 id number 3 ); Table created. Also a mentoned ...


2

It makes a lot of sense to me. The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses. Look at the first query select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ...


1

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table. These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded. An incremental clustered index will provide the best insert performance against ...


1

Index Scan scans each and every record in the index. Table Scan is where the table is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan. Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Index Seek: ...


1

Your max_connect_errors is way too low. You have it at 1000. That means after 1000 consecutive connect failures, you cannot connect to MySQL any longer. Your status variable Aborted_connects should be the dead giveaway if it climbs fast. When you can no longer connect to MySQL even with bunch of open DB Connections, you would have to execute FLUSH HOSTS; ...


1

How about an indexed view? CREATE VIEW dbo.LoginsByDate WITH SCHEMABINDING AS SELECT UserID, LoginDate = CONVERT(DATE, LoginDate), LoginCount = COUNT_BIG(*) FROM dbo.LoginTable GROUP BY UserID, CONVERT(DATE, LoginDate); GO CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate ON dbo.LoginsByDate(UserID, LoginDate); GO What this does is ...


1

For it worth : I"ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes. The optimize reduced the table sizes by 90% and improved the system performance by more than twice !


1

I think it could be helpful for you to think of MapReduce as (essentially) a distributed query engine. I know it isn't one-to-one, but with SELECT and aggregate functions such as sum(), max(), etc being very much like a Map() operation, and GROUP BY being very much like a Reduce(), there is quite a bit of similarity. In your case, on the same hardware, I ...


1

As only fldC & fldB are in the filter criteria, an index with these two columns on the left side will be right. Try to index on (fldC, fldB, fldA), this should do the trick mostly.


1

You could always test this against your data and possibly use some sort of query analyzer. Since there are a lot of factors for "common considerations about indexes," you should consider things like: data size, data type, and inserts, updates, deletes and not just selects. How often do you run the query? Is this an occasional check for blanks? How often ...


1

Edit: not mentioned so far.... Check for auto-growth events: http://stackoverflow.com/questions/3752942/how-to-check-when-autogrowth-is-done-last Check the auto-growth settings. Make sure they are appropriate for the size of your database. If your files were shrunk you may be experiencing log or data file auto-growth in tiny increments causing queries to ...


1

I think I am starting to understand. When I asked you to run SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1; You said it was 2015-07-13 15:11:56 which you have in your WHERE clause When you did the query select sum(diff_ms) from writetest_table; It performed a full table scan of 35.8 million rows. When you did the query select ...


1

Also worth reading: Best Practice in File Storage while Building Applications - Database (Blob Storage) Vs File System BLOB Storage as the Best Solution For better scalability. Although file systems are designed to handle a large number of objects of varying sizes, say files and folders, actually they are not optimized for a huge number (tens of ...


1

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated. Trigger: DELIMITER $$ DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$ USE `db2`$$ CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN SET @UserVerification=(select reps.veeva_rep_id from db1.reps where ...


1

This is a pure guess as I cannot find anything to support this but I believe that the reason it is much slower when you perform it on your local box is that it is going through the iteration on your local box. If you did this as a cursor I believe this would all process from SQL and be much faster. AKA GO 10000 is being iterated 10000 times from your local ...


1

Good answer from Rolando. In addition -- Triggers should not be used for logic, because a couple of inter-relating triggers later, things will get confusing fast. A nice set of instructions in a stored procedure or client side procedure can get across the business logic more clearly than a bunch of hidden logic in the database. There are also limitations ...


1

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark. Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you : SET sql_log_bin = 0; ...



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