Over the years I have read a lot of people's opinions on how to get better performance out of their SQL (Microsoft SQL Server, just so we are all on the same page...) queries. However, they all seem to be tightly tied to either a high-performance OLTP setup or a data warehouse OLAP setup (cubes-galore...). However, my situation today is ...
I run the very same query on two almost identical database. The only difference is that the first database has ID entries from 1 to 9000 for 2 tables while the other is in the 458231044 and 103511044 range for the 2 same tables. (for the same 9000 entries) The query compares ID and UNIX time numerous times. Running it on first database...
Hello, I am having problems using MySQL's fulltext search and returning the results in order by relevance. I have to use boolean full text search, which does not return results in order by relevance. However, I need these results in order of relevance. If I attempt to add an order by clause on the end of the query, the query results to ...
I am having trouble writing a query on a mysql table of user activity that will give me a breakdown of how active our users are. The table structure is like so: CREATE TABLE IF NOT EXISTS `ca_activity` ( `id` bigint(20) NOT NULL auto_increment, `user_id` bigint(20) default NULL, `activity_type` varchar(50) collate utf8_unicode_ci...
We have a non normalized table that contains foreign key infomration as free text inside a column. I would like to create a view that will transform and normalize that table. E.g. a column that contains the following text: "REFID:12345, REFID2:67890" I want to create a view that will have REFID1 and REFID2 as 2 separate integer colu...
Hi, I am trying to pull multiple columns from 3 joined tables, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key in the data entry table). The issue i have is that there could be two data entries, numbered 1 and 2, belonging to a pet - the query has to just pick the data entry...
I'm using .NET 4 and the Entity Framework to construct a simple query. Here's the C# code: return Context.Files.Where(f => f.FileHash != 40) .OrderByDescending(f => f.Created) .Take(5); When I trace the query using ObjectQuery.ToTraceString(), I find the following subquery: SELECT TOP (5) [P...
Hi there, Well, I think my question says it all. I need to know if Groovy SQL supports two phase commits. I'm actually programming a Grails Service where I want to define a method which does the following: Get SQL instance for Database 1, Get SQL instance for Databsae 2, Open a transaction some how: Within the transaction call two dif...
Here's my query: select * from test n WHERE lower(process_name) like 'test%' AND ( test_id is NULL OR TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE),-6),'YYYYMM') > TO_CHAR(n.process_date,'YYYYMM') I want check whether date field process_date is greater than 6 months in the query. ...
I'm trying to write a PHP script for 'long-polling', returning data when new rows are added to a (Postgres) database table. Is there any way to get a SELECT query to return only when it would return results, blocking otherwise? Or should I use another signaling mechanism, outside of the database? ...
This has me pulling my hair out. We have a workflow, hosted as a WCF service, which makes a call to another WCF service which then calls a stored procedure. Store procedure calls a merge, then iterates through a cursor that calls another sproc. The cursor count is the same as the source count in the merge. If the source count is high...
I have a table that maps a user's permissions to a given object. So, it is essentially a join table to 3 different tables. (Object, User, and Permission) The values of each row will always be unique for all 3 columns, but not any 2. I need to create a non-clustered index. I want to put the index on the foreign keys to the object and u...
Need to query a database for 12 million rows, process this data and then insert the filtered data into another database. I can't just do a SELECT * from the database for obvious reasons - far too much data would be returned for my program to handle, and also this is a live database (customer order details) and I can't have the database ...
Hello, I'm having trouble trying to define the SQL query for this table: There's a table of patients and their weight readings recorded on visits with the following columns: patient ID weight reading visit ID (one per visit) In other words, if in two records two visit IDs are the same, then two weight readings have been taken on ...
SELECT a FROM b UNION ALL SELECT a FROM c UNION ALL SELECT a FROM d Does UNION ALL guarantee to print out records from tables b, c, d in that order? I.e., no records from c before any from b. This question is not for a specific DBMS. ...
I have a table in my database representing releases of software; the relevant columns of the schema being as follows: ProductID int PlatformID tinyint Date datetime All are not null, and there is also a unique key assigned to the first two columns. I would like to build a query (using LINQ) which returns the latest release ...
I have my database with the columns as follows: keyword, part1_d1, part1_d2 ........ part1_d25, part2_d26, ......part2_d34 FYI: d1 through d34 are documents.. How can I give a query to obtain columns with column_name like '%part1%'; as below keyword, part1_d1, part1_d2, ........ part1_d25 I tried the query: select (Select COLUMN...
Is there a way to get a column indicating the number of NULL fields in a row? This would be within a SELECT statement. For Example: Field1 Field2 Num_Null ----------------------- NULL "A" 1 UPDATE: I want this query so I can sort based on how many Affiliates sales there are of a given Book. So having 3 affiliates would b...
I have a stored procedure on SQL Server 2000. It contains: select ... into ##Temp ... ... drop table ##Temp When I run the stored procedure with ADO a second time, it prompts: There is already an object named '##Temp' in the database. Could anyone kindly tell me what's wrong? ...
I want to import data from an Excel file - assume Excel 2003 / .xls - to Sql Server 2008. Have tried adding a linked server to the JET OLE DB Access driver, and of course it fails on the 64-bit machine. But when I try to drop the linked server while experimenting, there's another error saying that the linked server already/still exists!...