A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

3
votes
1answer
207 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
1
vote
1answer
67 views

Query for View with unique entries from Database with repeated entries

I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any ...
4
votes
1answer
327 views

performance issue caused by IO?

Just curious to get an issue sanity checked . . . I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server. Looking at ...
0
votes
2answers
87 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
1answer
47 views

Query Optimization for added value

I have a query that's behaving a bit oddly. In my database I have a table called "records". It tells me a bunch of information about what applications a user ran on my company's machines. I'm ...
0
votes
1answer
55 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
1answer
28 views

Condense Query Results with Wildcard

I have the following query: select programVersion, count(programVersion) from records R inner join Programs P on P.programID=R.usageProgramID where programName="word" ...
2
votes
2answers
289 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
2
votes
1answer
164 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
2
votes
1answer
113 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
2answers
185 views

How to avoid timeouts during big DML operations

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again. The problem I have is that this runs for about 4 hours ...
1
vote
0answers
128 views

Optimising Large SQL Server Tables [closed]

I have a large table with around 50 million rows and 15 columns. Whenever I read, I always need all columns so I can't split them. I have a clustered index on the table with 4 keys (ALL INT) and I ...
13
votes
2answers
3k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
2
votes
1answer
1k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
1
vote
2answers
66 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
1
vote
1answer
3k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
0
votes
2answers
112 views

Help creating this query

I'd like to integrate the following query: SELECT parent.id , COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child ON child.parent_id = parent.id ...
0
votes
1answer
86 views

How to optimize query?

I need to optimize the following query: SELECT DISTINCT column1, column2 FROM tbl_name WHERE column3 = 'x' AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) AND TRIM(UPPER(column5)) = ...
1
vote
0answers
145 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
2
votes
2answers
475 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
1
vote
1answer
615 views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, ...
2
votes
2answers
6k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
4
votes
1answer
230 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
4
votes
1answer
1k views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
1
vote
2answers
623 views

Obvious inefficiencies in this Netezza query?

Why might this query be taking nearly 2 hours to run when similar queries complete within a minute? CREATE TABLE T3 AS SELECT A.*, COALESCE(C.FIELD1,0) AS FIELD1A, ...
0
votes
1answer
1k views

Speeding up MySQL NOT IN (Subquery) [duplicate]

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed ...
-1
votes
1answer
117 views

Query performance in SQL Server [closed]

What factors should I check when I compare the performance of a query? I saw many articles stating about IO cost, subtree cost (by using Execution plans), statistics such as CPU time, elapsed time ...
2
votes
2answers
224 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
2answers
88 views

Query performance differs greatly between a development setup and production

I'll try to keep this question straightforward, though I am dealing with a big ball of mud. When I run my test query acrossed linked servers both located locally (on a shared virtual host), the ...
-1
votes
2answers
4k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...
7
votes
1answer
1k views

Estimated vs Actual rows differences (actual much smaller than estimated) - sort

I'm running a query that is processing some nodes out of an XML document. My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some ...
1
vote
2answers
952 views

Update performance: clustered versus covering index

I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
1
vote
1answer
720 views

Is it possible to avoid filesort?

Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it. I tried adding indexes, for both top_expire and program, but didn't ...
15
votes
1answer
3k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
5
votes
4answers
26k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
0
votes
1answer
543 views

Filtering records not present with specific conditions in another table

I have a table (let's call it extra_info) in MySQL that has various extra infomation/labels on user accounts: account_id / data_key 11 / 'test' 11 / 'blah' 12 / 'coupon a' 12 / 'coupon b' 15 / ...
0
votes
0answers
58 views

Faster way to GROUP or ORDER dynamic fields?

We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results. The table has around a million results and is growing. The app has been ...
1
vote
2answers
193 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
3
votes
2answers
6k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
7
votes
1answer
1k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
0
votes
2answers
221 views

How can I increase performance for select in select statement

The item table is like this id category_id name registered I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items ...
-1
votes
4answers
146 views

Dropping and recreating indexes [closed]

I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness). I somehow manage to rebuild ...
0
votes
0answers
521 views

Properly index magento mysql tables to improve specific query performance

I am running magento community edition and its db performance is horrible. I have the following query: SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
-2
votes
1answer
1k views

what's the fastest method to export a table through sql query ? 11g - performance

I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
1
vote
3answers
3k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
3
votes
3answers
144 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
0
votes
2answers
511 views

how to find similar word with more similarities

how to Find words with length less than or equal... declare @inp nvarchar(max),@data nvarchar(max) set @inp='You can dance, you can jive, having .... jove... jve, ...' set @data = 'jeve' ...
0
votes
2answers
974 views

How to use and optimize subquery on 100 million rows

What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
2
votes
2answers
1k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
2
votes
4answers
5k views

How can I query data from a linked server, and pass it parameters to filter by?

I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned. The basic syntax looks something like this: INSERT INTO #tmpTable (Id, ...