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

17
votes
1answer
5k 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 ...
6
votes
4answers
37k 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
684 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
61 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
235 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
7k 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
2k 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
246 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
155 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
658 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
145 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
772 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
1k 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 ...
3
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
8k 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, ...
1
vote
1answer
82 views

Query optimization when no row is returned

I am using explain to figure out what is happening in my query which is: explain select t from c where u1_id = 1 group by t; I see "Using where" in the Extra column of the resultset. But this only ...
0
votes
1answer
53 views

Optimizing Query

Can anyone help me tuning this query..... explain extended SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a LEFT OUTER JOIN (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY ...
0
votes
1answer
128 views

What is the correct model for related tables with millions of rows?

I need to create a question and answer tables that will have millions (maybe billions) of rows. The current model is: Question Table id_question (PK, auto increment) id_user question_content ...
0
votes
0answers
615 views

SQL nested select (query rows after variable), running slow

Have large table (Sybase) rows = 6276225. I'm pulling call status and my query is very slow. In effect what I’m trying to do is pull data for any event after my variable. While querying for just one ...
0
votes
1answer
530 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
1answer
31 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
2
votes
2answers
154 views

Different queries result in big difference in performance

I have 2 similar functions, different way result in big diff in performance. The PostgreSQL version: 9.2.1. Function 1 create or replace function func_1() returns text as $$ declare v_i ...
0
votes
1answer
1k views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
5
votes
3answers
3k views

Query runs slow in test site on first execution. Why?

I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
4
votes
2answers
3k views

Is high Extent Fragmentation a problem?

DBCC SHOWCONTIG scanning 'MyTable' table... Table: 'MyTable' (2048062382); index ID: 1, database ID: 28 TABLE level scan performed. - Pages Scanned................................: 1019182 - Extents ...
5
votes
1answer
640 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
0
votes
1answer
327 views

Database schema design help needed

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance. This application ...
0
votes
0answers
123 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
0
votes
1answer
100 views

speeding up a query on MySql [closed]

I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query. create table t2 select field_1, sum(ifnull(field_2,0)) ...
5
votes
1answer
3k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
7
votes
1answer
28k views

How to speed up queries on a large 220 million rows table (9 gig data)?

The issue: We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in ...
6
votes
2answers
392 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
6
votes
4answers
1k views

Gaps and islands: client solution vs T-SQL query

Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client? To be specific, let us provide some test data: CREATE TABLE dbo.Numbers ( n INT NOT NULL ...
4
votes
2answers
1k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
1answer
61 views

What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this? SELECT id FROM table WHERE table.int_col > 1; I'm specifically interested in ...
0
votes
1answer
448 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
0
votes
1answer
92 views

Speed efficient query for membership first joined, latest category from membership table (min, max)

I have the following table representing membership information: CREATE TABLE IF NOT EXISTS `membership` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organisation_id` int(11) NOT NULL, ...
6
votes
1answer
324 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
1
vote
1answer
180 views

SQL Server 2008 search joining query

I have two table as one is the message table and another one is messageUser table. Now i need to check before insert a new row. for example, M_MessageMessageId========= ...
-2
votes
1answer
375 views

Second time query execution using different constants makes faster? [closed]

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
4
votes
1answer
765 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
1
vote
2answers
89 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
0
votes
1answer
129 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
1
vote
0answers
465 views

SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table

SOLVED. I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000; Now the queries are running very fast, down to ...
2
votes
3answers
2k views

MySQL: can not get rid of “Using filesort” in a simple query

Simple query but can not get rid of "using filesort": CREATE TABLE IF NOT EXISTS `online` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `expiration` int(11) NOT NULL, ...
2
votes
3answers
522 views

Why is SQL running the same query longer from another connection? [closed]

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows ...
6
votes
3answers
5k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
4
votes
1answer
82 views

Covering Index Question

I have the following query Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED, PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND, CASE Left(PT.INVENTREFID, 3) WHEN ...