Hi all, I have the following query and it's not working exactly as i want it to and it is really slow so i figured i'd ask for some help. CREATE PROCEDURE [dbo].[SummaryReport] @event varchar(7) = null, @pet_num varchar(12) = null AS BEGIN WITH pet_counts AS (SELECT event, pet_num, pageid, ...
Hoping someone can help with this. I have a query that pulls data from a PHP application and turns it into a view for use in a Ruby on Rails application. The PHP app's table is an E-A-V style table, with the following business rules: Given fields: First Name, Last Name, Email Address, Phone Number and Mobile Phone Carrier: Each pro...
Background: I am parsing a 330 meg xml file into a DB (netflix catalog) using PHP script from the console. I can successfully add about 1,500 titles every 3 seconds until i addd the logic to add actors, genre and formats. These are separate tables linked by an associative table. right now I have to run many, many queries for each titl...
I have a table that has a foreign key to a table that stores some blob data. When I do an inner join on the tables with a condition on the main table the join type goes from 'index' to 'ALL'. I would like to avoid this as my blob table is on the order of tens of gigabytes. How can I avoid it? Here is the the basic inner join: EXPLAIN ...
Dears, I need your help to optimize the query below. Let us assume we have a web application for articles. The software use two table;one is the article table and the second one is the users table. The article table hold the date when the article is created,the id,the body,the title & the section. Let us assume that we have one section ...
Hi guys, I have HDF5 files with multiple groups, where each group contains a data set with >= 25 million rows. At each time step of simulation, each agent outputs the other agents he/she sensed at that time step. There are ~2000 agents in the scenario and thousands of time steps; the O(n^2) nature of the output explains the huge numbe...
Is there some reliable (i.e. acknowledged) data on the execution times for various queries, such as different types of joins, scans, with different types and size of data? I am looking for the order of magnitude. This does not have to be vendor-specific data. It should be similar in terms of presentation for latency for various access t...
What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +----+-------------+---------------------------+-----------------------------+ | id | select_type | table,type,possible_keys, | Extra | | | | key,key_len,ref,rows | ...
Hello, While implementing a hierarchy / tree structure on a SQL server 2005 database, I'm getting very slow query response ( below queries talking more than 5 sec ) when using LIKE clause combined with the EXISTS clause. The slow queries involve two tables - [SitePath_T] and [UserSiteRight_T] : CREATE TABLE [dbo].[UserSiteRight_T]( ...
I have a 800MB MS Access database that I migrated to SQLite. The structure of the database is as follows (the SQLite database, after migration, is around 330MB): The table Occurrence has 1,600,000 records. The table looks like: CREATE TABLE Occurrence ( SimulationID INTEGER, SimRunID INTEGER, OccurrenceID INTEGER, Occurren...
I have few questions on SQL.. How to analyze the performance of a query? Any software, inbuilt features of MSSQL server 2005/2008? What should be used in place of inin queries so that the performance is better? Eg: SELECT * FROM enquiry_courses WHERE enquiry_id IN ( SELECT enquiry_id FROM enquiries WHERE session_id = '4cd3420a1...
Suppose I have a SQL table of Awards, with fields for Date and Amount. I need to generate a table with a sequence of consecutive dates, the amount awarded in each day, and the running (cumulative) total. Date Amount_Total Amount_RunningTotal ---------- ------------ ------------------- 1/1/2010 100 ...
Hello, This question is linked to my previous one ( posted as an anonymous user - now I have an account ) and, before I begin, I would like to give the credit to Rob Farley for providing the right indexing schema. But the problem is not the indexing schema. It's the Query Optimizer ! The query : SELECT s.ID_i , s.ShortName_v ...
Hello, I am new to sql query optimization and i would like to know if there is anyone can suggest a profiling and optimization tool that i can use. I am trying to optimize queries running on mysql. Thanks for any help. ...
I have a deceptively simple SQL Server query that's taking a lot longer than I would expect. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT COUNT(DISTINCT(guid)) FROM listens WHERE url='http://www.sample.com/' 'guid' is varchar(64) NULL 'url' is varchar(900) NULL There is an index on guid and url. There are over 7 million ...
I have many complex sql queries that I would like to optimize. The performance on it is pretty bad. Is there an online tool to optize such queries? ...
I am worried this is a bit expensive. Plus I will soon implement a normalized system for the tags so there will be additional joins. On top of that I have 4 tables (tbl_videos, tbl_articles, tbl_galleries and tbl_users) of which I want to display three results of each and thus will have to run the query four times on one press of 'se...
I'm doing a relatively complex CTE query with an expensive user defined scalar function call. The where clauses of the CTE filters most of the large table data, and returns relatively small result, so calculating an expensive field is not a problem, if calculated on the resultset. SQL server is doing a good job if not evaluating the ex...
Hello. Lets say i have the following scores table (total rows of 10): rank userid score ---- ------ ----- 1 |455 |10 2 |435 |9 3 |354 |8 4 |342 |7 5 |343 |6 6 |123 |5 7 |125 |4 8 |128 |3 9 |5 |2 10 |6 |1 the user can fetch his score status, and he can get 5 score positions around hi...
ALTER PROCEDURE ReadNews @CategoryID INT, @Culture TINYINT = NULL, @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @Start BIGINT, -- for paging @Count BIGINT -- for paging AS BEGIN SET NOCOUNT ON; --ItemType for news is 0 ;WITH Paging AS ( SELECT news.ID, news.Title, news.Description, news.Date,...