sql

Avoiding full table scan

Hello, I have the following query which obtains transactions from the transactions table and transaction detail. Both tables have a big amount of entries, so this query takes a while to return results. SELECT * FROM transactions t LEFT JOIN transac_detail tidts ON (tidts.id_transac = t.id); However, I'm more worried because of the f...

Implementing and indexing User Defined Fields in an SQL DB

I need to store a large table (several millions or rows) that contains a large number of user-defined fields (not known at compile time, but probably around 20 to 40 custom fields). It is very important (performance-wise) for me to be able to query the data based on those custom fields: i.e. "Select the rows where this attribute has that...

Tracing SQL queries created by Ruby-on-Rails

Hi, In the web application I am currently developing, I have quite a few database queries being performed. I would like to know what parts of the code are producing these queries so that I can perhaps refactor the code to reduce them. Is there an easy way to do this? Typically, the database queries are like: SELECT count(*) AS count_a...

UNION ALL versus CONNECT BY LEVEL for generating rows

I was wondering which is a better/faster/more efficient way of turning arbitrary strings into columns: UNION ALL SELECT my_field, CASE WHEN my_field = 'str1' THEN ... ... END, ... FROM ( SELECT 'str1' AS my_field FROM DUAL UNION ALL SELECT 'str2' AS my_field FROM DUAL ...

SQL/MySQL structure (Denormalize or keep relational)

I have a question about best practices related to de-normalization or table hierarchy relationships. For a simple example, let's say I have an app that allows a user to make a payment for an order. I save the order information in the orders table, and I have another table for the payment called payments. Payments has a foreign key to th...

MySQL conditional SELECT statement

If there are records that have a field containing "X", return them, else return a random record. How the heck do you do this? ...

SQL Server Script to create a new user.

I want to write a script to create a admin user ( with abcd password )in SQL Server Express. Also I want to assign this user (admin) full rights ...

Join instead of correlated subquery

CREATE TABLE BlogPosts ( PostID INT PRIMARY KEY not null, PostTitle NVARCHAR , BlogID int, TotalComments int ) May this query be simplified with any Join instead of correlated subquery? SELECT TOP 5 * FROM BlogPosts as t0 WHERE t0.PostID = (SELECT TOP 1 t1.PostID FROM BlogPosts as t1 WHERE t0.BlogID = t1.BlogID ORDER BY...

How can I SELECT 16 records at random from a range of rows in MySQL?

I want to display a list of 16 of the most popular items in my database, but I want that list to be different every time. So from say, the top 50 downloaded items, choose 16 at random and return that in the result. Is that possible with just one query? ...

SQL Server Union of a single label header and a boat load of data columns

I have a SELECT * FROM #tmp and it returns 131 records. I wish to prepend this with a label so the result is format=transaction, followed by the rows each containing 131 columns. It is cumbersome to do the following; is there a nicer method? SELECT 'format=transaction', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,...

SQLite Reset Primary Key Field

I have a few tables in SQLite and I am trying to figure out how to reset the autoincremented database field. I read that: "DELETE FROM tablename" should delete everything and reset the autoincremement field back to 0 but when I do this it just deletes the data. When a new record is inserted the autoincrement picks up where it left off ...

Writing a complex trigger

This posting is an update (with trigger code) from my earlier posting yesterday I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRow...

How do I return the SQL data types from my query?

I've a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don't need nor want to understand. The result of this query needs to be stored in a staging table to feed a report. I need to create the staging table, but with hundreds of views/tables to dig through...

What's the easiest way to add an index on a live myISAM table?

I have a myISAM table running in production on mySQL, and by doing a few tests, we've found we can tremendously speed up a query by adding a certain compound index. So far so good. However, I am not really about the best way to add this index in a production environment without locking the table for a long time (it's got 27GBs of data, s...

MySQL - Join Based on Date

Is it possible to join two tables based on the same date, not factoring in time? Something like: ...FROM appointments LEFT JOIN sales ON appointments.date = sales.date... The only problem is it is a datetime field, so I want to make sure it is only looking at the date and ignoring time ...

Randomly selecting a limited number of records with two conditions in MySQL

I have a MySQL table with a column called "priority". The column can have two values: high or low. I want to select 8 records from the table at random, but I want 6 of them to be high priority, and 2 of them to be low priority. If possibly, I would like to do it in one SQL statement. Is there any way to do two LIMITS in one query bas...

Execution Plans for Databases

Question 1: When we execute a query, does the execution plan change for each and every time when executing the query? If yes, any performance hit? If no, then if we change something in the table, i.e adding an index, how does the databse know that there is something it can use to change the execution plan for faster execution? QU...

Using MySQL user-defined variable in stored procedure for 'in' clause

When sending a string of comma separated ids, as a varchar, to a MySQL stored procedure I can not use the string as part of an IN clause with the correct results returned. The string is truncated as a decimal and only the first value is being used. I thought I would be able to get around this by preparing and then executing the statemen...

Stored Procedure Timing out.. Drop, then Create and it's up again?

I have a web-service that calls a stored procedure from a MS-SQL2005 DB. My Web-Service was timing out on a call to one of the stored procedures I have (this has been in production for a couple of months with no timeouts), so I tried running the query in Query Analyzer which also timed out. I decided to drop and recreate the stored pro...

How to manage SQL Connections with a utility class?

We have a SQL utility class that takes the name of a stored procedure an its input parameters, and returns the results in datatable. The reasoning behind this is so that we don't have to worry about forgetting to close connections and having connection leaks. Also so that we can reduce code by not having to recreate datadapters and dat...