query-optimization

delete records from table using another table?

Hello note: to the editors: please edit the title if have a better one :) my question is: I have two tables in my database ----------- | table1 | |----------| | id | |text | =========== ----------- | table2 | |----------| | id | |text | ==========...

I need the most recent record in a join (PostgresSQL)

I have a table like so: call_activity ( call_id TEXT, activity_type TEXT, activity_time TIMESTAMP, PRIMARY KEY(call_id, activity_type, activity_time) ) activity_type may be one of about 9 different strings: 'started' 'completed' (about 5 variations on this) 'other' (these are the states that I want to display) A...

Linq2SQL "or/and" operators (ANDed / ORed conditions)

Let's say we need to apply several conditions to select from a table called "Things" (unknown count and nature) if conditions are known, we can write db.Things.Where(t=>foo1 && foo2 || foo3); but if we have to build that Where condition programatically, I can imagine how can we apply ANDed conditions IQuerable DesiredThings = db.Thi...

How can I analyse a Sqlite query execution?

I have a Sqlite database which I want to check the indexes are correct. MS SQL Analyser is great at breaking down the query execution and utilised indexes. Is there a similar tool for Sqlite? ...

MS SQL Server optimizer and varying table and field aliases

We have a lot of quieries for which we append a random alias at the end of field and table names (due to a custom ORM implementation that might be hard to change). The queries are like the following (though substantially more complex, most of the time): SELECT fooA.field1 as field1B, fooA.field2 as field1C FROM foo as fooA ...

Speeding up a SQL query with generic data information.

Due to a variety of design decisions, we have a table, 'CustomerVariable'. CustomerVariable has three bits of information--its own id, an id to Variable (a list of possible settings the customer can have), and the value for that variable. The Variable table, on the other hand, has the information on a default--in case the CustomerVaria...

What is a good way to optimize an Oracle query looking for a substring match ?

I have a column in a non-partitioned Oracle table defined as VARCHAR2(50); the column has a standard b-tree index. I was wondering if there is an optimal way to query this column to determine whether it contains a given value. Here is the current query: SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%'; I looked at Ora...

Optimize multiple joins with table functions

I would like to join several times with the same table function for different input variables in the same query. But this turns in my case out to be much slower than using table variables and selecting from the table functions separately. How can I avoid table variables and still have a fast query? For example, we have a SQL query like...

Query/Database Optimization: How to optimize this? (and should I use a materialized view?)

Hi -- I have a question on how to optimize a query. Actually, as I'm going to be running the query frequently, I was thinking of using a materialized or indexed view (is this a good idea here?) or denormalizing. Consider the following four tables (with irrelevant fields omitted): Users (int userId) Groups (int groupId) GroupMembershi...

Linq2SQL: Optimize query?

Can I optimized the following query in any way: The goal is to try to find clients of type 4 that also exists as type2 based on their VAT and Email. A client can have one or more clientusers. (I have all the appropriate indexes set, I promise) from e in Clients.Where(h => h.ClientTypeId==4) join u in ClientUsers on e.Id equals u.Clie...

Is storing counts of database record redundant?

I'm using Rails and MySQL, and have an efficiency question based on row counting. I have a Project model that has_many :donations. I want to count the number of unique donors for a project. Is having a field in the projects table called num_donors, and incrementing it when a new donor is created a good idea? Or is something like @num...

Where to patch Rails ActiveRecord::find() to first check collections in memory?

For somewhat complicated reasons, I would like to create something that works like this: # Controller: @comments = @page.comments # comments are threaded # child comments still belong to @page ... # View: @comments.each_root { display @comment { indent & recurse on @comment.children } } # alternatives for how recursion call m...

T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all. I've tried it the following two ways: First way: SELECT field1, field2...etc FROM my_view WHERE parm1 = CASE WHEN @PARM...

Forcing a SQL Remote Query to filter remotely instead of locally

I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this: SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > '1/1/2009' then the filter is applied remotely... However, I don't actua...

MYSQL IN or multiple conditionals

I have a simple query which is returning records based on the field status not having certain values. Lets say for arguments sake that the field can have values 1,2,3...10 and I want to return all records that don't have values 3, 7 and 9. Which of the following would be best to use? Option 1. SELECT `id` FROM `tbl` WHERE (`status` != ...

PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results. I understand that LIKE queries are very resource intensive but I just don't und...

Is there a more efficient way than doing several JOINS (MySQL)?

I have three tables: USER: user_id (pk); username FIELD: field_id (pk); name METADATA: metadata_id (pk); field_id (indx); user_id (indx); value The reasoning for this is that the application allows custom fields to be created for each user. To display all the information for a user I am building a dynamic query (via PHP) which ends u...

Filter out rows by hardcoded list in MySQL performance

Hello, I have a hardcoded list of values like: 1,5,7,8 and so on. And I must filter out rows from table that have ID in list above, so I do something like this: Select * from myTable m left join othertable t on t.REF_ID = m.ID where m.ID not in (1,5,7,8...) But when I have more values (like 1000) and more rows (100) in other...

mysql query with select - where - in - join

Which is the better way of retrieving multiple rows? select * from order_details where order_id in (1,2,3, ... .... ) ...or: select * from order_details INNER JOIN orders on orders.id = order_details.order_id INNER JOIN customers on customers.id = orders.customer_id where customers.id = 3 ...

Two left joins and one query to MySQL performance problem

Hello, I'm designing a project for quizzes and quizz results. So I have two tables: quizz_result and quizz. quizz has primary key on ID and quizz_result has foreign key QUIZZ_ID to quizz identity. Query below is designed to take public quizzes ordered by date with asociated informations: if current user (683735) took this quizz and has...