I have a report in SSRS 2005, with an embedded SQL query (no stored procedure). This report is running slower in SSRS than the query will run in SSMS. I'm wondering how to troubleshoot this, and if there are any differences in the way query optimization works for a report with embedded SQL code vs. a stored procedure. Thanks! ...
Hi, I work at a fairly big website; we have ~400-500 million page views a month. We use PHP and MySQL. Currently our page flow works like this (semi pseudo code for clarity): mysql_connect(); mysql_select_db('red'); mysql_query('SELECT * FROM apples'); mysql_query('SELECT * FROM cakes'); One of my co-workers suggested that mysql_sele...
Hello all I'm trying to optimize this mysql query using EXPLAIN. Can somebody please help me out over here? EXPLAIN SELECT * FROM keyword WHERE keyword LIKE "panasonic%" AND keyword != "panasonic" AND price < 3230 AND price > 3370 ORDER BY price DESC LIMIT 99 Basically I want to find out the keywords which start with "some keyword" b...
I know how to write SQL queries and can get the results I need. But sometimes my queries are slow and I don't fully understand why. What are some good resources for learning to write efficiant queries and to optimze the quries I've allready written? ...
I need to get summary data from many many rows. The summary fields are counts of how many entries have each value for different fields. For example, a table with people's age, city, job, etc, the summary data would include fields like "countManager", "countCodeMonkey" for each job, and then "countChicago", "countNewYork" etc for cities. ...
I've been trying to perform a join on two tables in MySQL, and the query will run for a minute or two before I run out of memory without getting results. I'm far from a database expert, so I'm not sure if I'm writing my queries poorly, if I have some MySQL settings poorly configured, or if I really should be doing something else entirel...
Background I've got a complex page caching system in place. Every database item that is used on a page stores a reference related to the item and the cached page. Below is the database table used to store those cached references CREATE TABLE `sacrifice_cache` ( `cache_id` int(21) NOT NULL AUTO_INCREMENT, `cache_page_id` varchar(255...
How can i compare query X and Y and say that X is better then Y when they both take almost the same time in small cases scenarios??? the problem is that i have 2 queries that are suposed to run on a very big database, so run a evaluate is quite not a option (aka try and fail method), so we created a small database to perform some tests....
Hey there, I'm kinda stuck trying to optimise a query which has a NOT EXISTS clause on a nested SELECT. I've been rewriting my queries containing nested selects, changing them to joins, but on this occasion I'm not sure how to combine that with the NOT EXISTS clause. I have the following query: SELECT `reg_no`, COUNT(*) AS `records_foun...
I have a large database (180k+ rows and growing fast) of location data and am plotting them on a google map. For a given view port, I just want to serve up a sample of 100 applicable points. The database is therefore queried by lat/lng, but if I put an index on these rows the problem is that the sample of 100 points will be either at t...
Ok so I have three tables like so Students Name StudendId ---------------------- John 1 Jane 2 Bob 3 Betty 4 Class StudentId TeacherId ---------------------- 1 1 2 2 3 1 4 2 1 3 4 3 Teacher Name Teacher...
Hello everyone, I have a complex SQL query with nested 'subqueries' that I want to optimize SELECT 'likes' `type`, entry_date item_date, CASE `type` WHEN 'entry_id' THEN entry_id WHEN 'member_id' THEN f.member_id WHEN 'topic_id' THEN entry_id END AS item_id, CASE `type` WHEN 'entry_id' THEN (SELECT title FROM ex...
I have a Table foo which records the sightings of bird species. foo_id is its PK, other concerned columns are s_date, latitude and longitude. species_id is its FK. I have indexes on s_date, latitude and longitude, species_id. Table foo has 20 million records and increasing. The following query gives me top 10 latest species sightings in ...
In SQL Server 2005, given the following resultset ID | InstanceNumber | IsArchived 5000 | 1 | True 8347 | 2 | True 9343 | 3 | False 11048 | 4 | False What I would like to return is this: ID | InstanceNumber | IsArchiv...
Tables: CREATE TABLE IF NOT EXISTS `posts` ( `post_n` int(10) NOT NULL auto_increment, `id` int(10) default NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`post_n`,`visibility`), KEY `id` (`id`), KEY `date` (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; CREATE TABLE IF NOT EX...
This is more of a preference but I was wondering what people think would be the optimal option to perform. I have a Question, Answer, and Point (because I need to track which user made the point) Table Dump Question: id title Answer: id question_id user_id response Point_Answer: id answer_id user_id points So in...
I have a table for settings, which can be shown as: object_id (int) key (varchar) value (text) I am trying to grab the object_id that has key's equal to 2 items. SELECT `object_id` FROM `settings` WHERE `key` = 'A' AND `key` = 'B' I know that won't work, the only way I can think of doing this is joining it on itself: SELECT a.`objec...
Hello all, long time lurker, first question! I am struggling to optimize this query, which selects the lowest priced items that match the chosen filters: SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link FROM product_info NATURAL JOIN (SELECT * FROM product_all WHERE product_all.date = '2010-09-30')...
Hello! Sometimes I want to generate INSERT statements from the contents of a database table. With SQLite, I can do: SELECT 'INSERT INTO foo (col1, col2) VALUES (' || quote(col1) || ',' || quote(col2) || ');' FROM bar; With Oracle, I have to do : SELECT 'INSERT INTO foo (col1, col2) VALUES (''' || replace(col1, '''', '''''') || ''...
$sql="update users_contain set wood_max = (Select building_production from building_level where merge_id=$subPrimaryKey and empire_id=$user_empireID) , iron_max = wood_max, clay_max = wood_max where user_id = $user_id"; Now there is a question. will wood_max will always be updated first than iron_ma...