sql

How can I speed up this SELECT CONCAT/GROUP BY query?

I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example: SELECT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude) FROM places WHERE city='New York' AND state='NY' GROUP BY location There's going to ...

Working with the SQL Server XML data type

I've got a table which has a XML field. The typical XML it contains is; <things> <Fruit> <imageId>39</imageId> <title>Apple</title> </Fruit> <Fruit> <imageId>55</imageId> <title>Pear</title> </Fruit> <Fruit> <imageId>76</imageId> <title>Grape</title> </Fruit> </things> In my table i've got around 50...

SQL Join with CASE and WHERE

I have three tables - one for shipping rates, one for products and one for exceptions to shipping rate for particular products. Shipping is charged as follows: Each product has a shipping price, but this price can be overridden by an exception. If no exception exists for a product, the default rate is used for the shipping rate selected ...

SQL to compare two unix timestamps?

Hi, I have a table with a field that contains unix timestamps for each record. I would like to return each row where the year and month are equal to the current year and month. Is there a simple way to do this? Thanks ...

What is a dangerously high number (or rate of increase) for Handler_read_rnd_next?

This is related to the queries I'm running from this question, namely: SELECT CONCAT_WS(', ', city, state) AS location, AVG(latitude), AVG(longitude) FROM places WHERE state='NY' AND city='New York' GROUP BY state, city I've been looking at phpMyAdmin and they have one value red-flagged, Handler_read_rnd_next. ...

SQL code to insert multiple rows in ms-access table

I'm trying to speed up my code and the bottleneck seems to be the individual insert statements to a Jet MDB from outside Access via ODBC. I need to insert 100 rows at a time and have to repeat that many times. It is possible to insert multiple rows in a table with SQL code? Here is some stuff that I tried but neither of them worked. Any...

SQL - WHERE AGGREGATE>1

Hi, Imagine I have a db table of Customers containing {id,username,firstname,lastname} If I want to find how many instances there are of different firstnames I can do: select firstname,count(*) from Customers group by 2 order by 1; username | count(*) =================== bob | 1 jeff | 2 adam | 5 H...

What are the benefits of putting a default value in a column?

If this question is too broad, my apologies. I am a DBA and I am working with a developer that thinks column defaults are a bad idea and that just setting the column to disallow nulls is good enough. I am looking for the benefits of column defaults from the developers point of view. Thank you for your comments. ...

SSIS (missing) Pre-Build and Post-Build

For the warehouse work under progress, we have a single solution with multiple projects in it OLTP Database Project Warehouse Database Project SSIS ETL project After the SSIS project is built, I want to move the binaries (XML, really) from the Bin folder to "C:\AutomatedTasks\ETL.Warehouse\" and "C:\AutomatedTasks\ETL" I cannot find...

is there a SQL Prompt like plugin for VS 2008

I like to use the SQL Tools in VS but I would like to have some intellisense for SQL, with sql manager I can get the Redgate SQL prompt but I would like to stay in VS, any Ideas? Edit: is there anything other then Redgate SQL prompt? that dose error checking like the way R# or dev express? ...

Merging two tables

I have two tables with the following columns (identical columns in both tables): FunctionName, FrequencyCount I want to merge these two tables into a final table with columns: Function Name Base Frequency count Compared Frequency count Delta of Frequency count Merge operation should happen as follows: If [FunctionName] is in Ta...

Multi-threading & db record locks

Need help big time .... I need to create a .net application that will perform some bulk operations on , say around 2,000,000 records, in a table. There is a window of opportunity in which the application should run and try to process as many rows as it can. I am thinking that if I can have multiple threads of the app take 2000 records...

ETL as a transaction

For all the ETLs I have written so far, I have never made them transactions - i.e. if table 4 fails, roll everything back. What is the best practice in this regard? To "BeginTran + Commit" or not to "BeginTran + Commit" EDIT: I have one master package calling 4 other packages - is it possible to roll them all up into one transaction? ...

Registration script tips

Register.php if (isset($_POST['submit'])) { $username = mysql_real_escape_string(trim($_POST['username'])); $email = mysql_real_escape_string(trim($_POST['email'])); $passwd = mysql_real_escape_string(trim($_POST['passwd'])); if (empty($username)) { die ('You need to enter a username.'); }...... else insert } ?> Is it...

How do I get the primary key(s) of a table from Postgres via plpgsql?

Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function? ...

RAND not different for every row in T-SQL UPDATE

I have the follow T-SQL to update a table with test data: UPDATE SomeTable SET Created = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) , LastUpdated = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) I want it to pick random daes in the past year, unfortunately it uses the same date for every row. what is the best way to get it to...

DB2 Stored Procedure DBVisualizer

I am using DBVisualizer to execute a z/os DB2 stored procedure. Call DB001.MYTEST2('NY', 'Brooklyn', '11229', '2009-07-31'); I get this error message, Literal replacement parsing failed for procedure call to DB2 for z/OS. ERRORCODE=-4463, SQLSTATE=42601 Has anyone seen this before? ...

Is having a stored procedure that calls other stored procedures bad?

I'm Trying to make a long stored procedure a little more manageable, Is it wrong to have a stored procedures that calls other stored procedures for example I want to have a sproc that inserts data into a table and depending on the type insert additional information into table for that type, something like: BEGIN TRANSACTION INSERT...

Parameterize 'order by' in SQL

What's the proper way of parameterizing an order by clause in ADO.NET ? Sometimes there's a need to order by 2 columns, while the default is ordering on just 1 column, and sometimes you'll just want to change ASC to DEC. Is it considered ok to just use string concatenating in such cases (provided the input doesn't come from the user di...

SQL query for Top 5 of every category

Hi there, I have a table that has three columns: Category, Timestamp and Value. What I want is a SQL select that will give me the 5 most recent values of each category. How would I go about and do that? I tried this: select a."Category", b."Timestamp", b."Value" from (select "Category" from "Table" group by "Category" order by ...