sql

Optimum size of transaction in Postgres?

I'm running a process that does a lot of updates (> 100,000) to a table. I have the choice between putting all the updates in a single transaction or committing transactions every 1000 or so. Ignore for the moment the case where a transaction fails and is aborted. I'm interested in the best size of transaction for memory and speed effi...

innodb_file_per_table doesn't work

Hello I am trying to use the innodb_file_per_table option of my mysql 5.0. my relevant my.cnf is: innodb_data_file_path = ibdata1:100M:autoextend innodb_file_per_table innodb_autoextend_increment = 100 but when i drop & recreate my schema & DB, i see the split files for tables, but they are too small! (they dont start fr...

Which one have better performance : Derived Tables or Temporary Tables

Sometimes we can write a query with both derived table and temporary table. my question is that which one is better? why? ...

Using list alias in WHERE or GROUP BY clause

Hello, I'm writing a stored procedure containing SQL Select queries with some functions on some columns and a GROUP BY clause. Obviously, I have to repeat these functions in the GROUP BY, which dramatically decreases code readability and increases redundancy. So far, an alternative I have found was to use a derived query so I can acces...

Shorten and compact this small SQL snippet (Count and most recent date)

What would be the best way to shorten the following SQL Snippet: SELECT a.ViewCount, b.DateCreated FROM (SELECT COUNT(*) AS ViewCount FROM UserViewHistory uvh1 WHERE (UserID = @UserID) AND (ViewedByUserID = @RequestingUserID)) AS a, (SELECT TOP (1) DateCreated ...

Is it possible to export/import product categories in Magento?

I saw one other question on how to do a mass import of products, categories, and all that, but all I want to be able to do is import my categories from one store to another. When you export products I see that there is still a category ID attribute that it gives the products, but I don't see any options for exporting product categories....

How to make a view column NOT NULL

I'm trying to create a view where I want a column to be only true or false. However, it seems that no matter what I do, SQL Server (2008) believes my bit column can somehow be null. I have a table called "Product" with the column "Status" which is INT, NULL. In a view, I want to return a row for each row in Product, with a BIT column se...

Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `users` WHERE `userID` IN(SELECT `userID` FROM `users_indvSettings` WHERE `indvSettingID`=5 AND `optionID...

How to perform batch update in Sql through C# code

I want to update multiple rows like below update mytable set s_id = {0} where id = {1} (Here s_id is evaluated based on some complex logic). For performance reason, updates should happen in batches. Is there any way to batch the update statements and execute the batch through single execute statements? I know in JAVA we can do this ...

Foreign Key doesn't stop me entering data MySQL

I have a table which has one column as a foreign key joining to another table. It's a cricket question where I have a table called Fixtures and another called Inning. Inning table has a FixtureId column relates to the Fixture table. I would expect that If i do a insert on the inning table using a FixtureId that doesn't relate to a F...

What would cause a Fuzzy Lookup to return a Null set of values from the reference table?

I'm doing a fuzzy lookup on a view of a table which does a fine job returning similarities with the occasional exception, and I can't seem to figure out what is causing the problem. Every so often, the comparison will come up with null values from the lookup view, even though the values exist in both the view and the original table and t...

Table design options for large number of rows?

I have an application that sends data based on user interaction (not user input). The data sent could be an Integer, String, Date, or Boolean value. There are 140 keys. We may get anywhere from 1 key value pair to all 140 at a time. We want to store everything but will only be using 20 out of 140 keys within the application. The remaini...

Count entries across three tables based on month in SQL or LINQ

I would like to extract some data from three tables in a SQL Server 2005 database. While this can surely be done in code, it seems like this could be done reasonably well in SQL (bonus points for LINQ!). Basically, I would like to know for each month how many calls and meetings each employee has held with each of our clients. Somethin...

Using monthcalendar control, when i select a day and use that as a filter for my SQL it does not work as expected

I am trying to use the selectionstart and selectionend attributes from monthcalendar to filter between two login dates in a table. When I chose one single day, this does not work correctly. If i chose the day before and the day after the day in question then it will. The time values in my DB are like this: 2/23/2010 11:17:01 AM Both ...

SQL Update each record with its position in an ordered select

I'm using Access via OleDb. I have a table with columns ID, GroupID, Time and Place. An application inserts new records into the table, unfortunately the Place isn't calculated correctly. I want to update each record in a group with its correct place according to its time ascending. So assume the following data: ID GroupId Time ...

SQL - Mixing ANDs and ORs

Hello, I'm not a SQL Expert. I'm using SQL Server 2005 and I'm trying to figure out how to structure a query so that it can meet several requirements. I have two tables defined as follows: Classroom - ID - DepartmentID - TeacherName - RoomNumber Student - ID - Name - ClassroomID I'm trying to build a query that says, "give me...

Why does my ODBC SelectCommand get truncated when I add a parameter.

I am using an ODBC connection to retrieve a data set in C#. I can see that the full command string is added to the connection when it is created. OdbcDataAdapter dataAdapter = new OdbcDataAdapter(GetCommandString(), odbcConnection); I am using the following line to add the parameter to the command string. dataAdapter.SelectCommand.Pa...

select * from table where datetime in month (without breaking index)

I have a bunch of timestamped rows (using the 'datetime' data type) I want to select all the rows that have a timestamp that is within a particular month. The column is indexed so I can't do MONTH(timestamp) = 3 because that'll make this index unuseable. If I have year and month variables (in perl), is there a horrific bit of SQL I ca...

adding Lots items and single item to a shopping cart help!

I have a two different tables for two types of item list one called a single item list and another list called lot items that uses the same item name field from the single item list. Then the second table is used for the prices, sizes and amount of pieces per lot. the single item list is adding each item as expected, a single item come f...

SQL Server Logical Query Processing - how does it manage the huge datasets?

I'm doing some reading on SQL Server performance: http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=sr_1_6?ie=UTF8&s=books&qid=1267032068&sr=8-6 One of the surprising things I came across was how it processes the "FROM" phase in its Logical Processing. From what I understand, SQL Server will do the f...