Tag Info

Hot answers tagged

9

I've tried your query on a SQL 2012 instance and trace flag 4199 seems to fix the issue. With it enabled I get a merge join for a total cost of 0.24 and none of the extra branches. The specific KB article for this issue is Performance issues occur when the join predicate in your query has outer reference columns in SQL Server 2005 or in SQL Server 2008 ...


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 ...


3

Wow, surprised nobody answered this question in the past two years! The problem is almost certainly these two lines: log_statement = on # Log all statements log_per_node_statement = on # Log all statements # with node and backend informations They result in a lot ...


3

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist). In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. ...


3

The first thing I would check is the fnGetUSD -function. It looks like currency conversion, and you're now calling the function 5 times for every single row. That can be a huge performance issue. At least look into changing it to a inline table valued function (the multi-statement function will not help) or adding the calculation directly to this view. For ...


2

So I came to find out that my heroku instance wasn't in the same availability region as my rds instance. Once I changed it over to east coast the RDS DB was slightly quicker then my heroku DB. Incase anyone is stuck on how to create a rds instance in a different region through aws console, you need to click on region (i.e. Oregon) button in the top right ...


2

Well this answer depends on how your database is designed and licensed. If you have an Enterprise license, you should take a look at partitioning. This way you may partition your data which will help you in querying those table. If you make a good partitioning, you'll be able to just read a small portion of the table from the disc, instead of querying ...


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; ...


1

You can indeed use stored procedures to refresh the data in your database. It just means coding the INSERTs and UPDATEs appropriate to your data. If you are replacing the existing data with new data, you could use: INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty ...


1

Drawbacks of what you propose: Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM. Storing numeric 'values' VARCHAR makes it difficult or slow to test for. Key-value schema leads to ugly JOINs. And they are inefficient. In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks. This ...


1

What you are referring is not related to windows. It's for *NIX systems. For windows, you should monitor •\LogicalDisk\Avg. Disk Sec/Read •\LogicalDisk\Avg. Disk Sec/Write •\LogicalDisk\Disk Bytes/Sec •\LogicalDisk\Disk Reads/Sec •\LogicalDisk\Disk Writes/Sec •\LogicalDisk\Split IO/sec •\LogicalDisk\Disk Transfers/sec You can use PAL to analyze the ...


1

Points: RAID 5 is a poorly performing configuration for database files especially for writes, and Partitioning is even worse. Yes, it works and is reliable, but it's definitely a low-performance disk configuration that I would only use myself for development or the most light-weight of apps. The standard configuration for the physical host of a SQL ...


1

Even in your updated query, you hit FIELDDATA eight times and Events three times. Rather than focusing so much on indexes, let's refactor to reduce the number of table hits. See below for an example. This is just my best guess at a refactor without a full understanding of your data, so keep or ignore the bits that work for your data. Changes include: Use ...


1

O_DIRECT vs. ALL_O_DIRECT -- depends on OS, HDD vs SDD, and distribution of files across multiple filesystems. Probably ALL_O_DIRECT is best for your config. Was it previously "ext4"? That is not necessarily as good as XFS or ZFS. What is the value of innodb_file_per_table when doing CREATE TABLE? OFF (the old default) led to writing the data and indexes ...


1

TEXT fields have what the MySQL Documentation (Compressing BLOB, VARCHAR and TEXT Columns) calls "off-column storage" In a clustered index, BLOB, VARCHAR and TEXT columns that are not part of the primary key may be stored on separately allocated (“overflow”) pages. We call these “off-page columns” whose values are stored on singly-linked lists of ...


1

Recent expensive queries in the Activity Monitor in SSMS should be your first stop. For more information grab SP_Whoisactive and find Glenn Berry's diagnostic queries which will help you a lot. The exact query depends on the version of SQLServer but this should be a start. SELECT TOP(50) creation_time ,last_execution_time ,(total_worker_time+0.0)/1000 ...



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