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...
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...
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...
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
...
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...
If there are records that have a field containing "X", return them, else return a random record.
How the heck do you do this?
...
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
...
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...
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?
...
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,...
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 ...
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...
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...
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...
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
...
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...
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...
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...
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...
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...