Search Results

Search found 36773 results on 1471 pages for 'sql statement syntax'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 711/1471 | < Previous Page | 707 708 709 710 711 712 713 714 715 716 717 718  | Next Page >

  • error invoking store procedure with input parameter from ADO.Net

    - by George2
    Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong? System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied. class Program { private static SqlCommand _command; private static SqlConnection connection; private static readonly string _storedProcedureName = "Pr_Foo"; private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB"; public static void Prepare() { connection = new SqlConnection(connectionString); connection.Open(); _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public static void Dispose() { connection.Close(); } public static void Run() { try { SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101); Param1.Direction = ParameterDirection.Input; SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100); portal_SiteInfoID.Direction = ParameterDirection.Input; SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200); portal_RoleInfoID.Direction = ParameterDirection.Input; _command.ExecuteScalar(); } catch (Exception e) { Console.WriteLine(e); } } static void Main(string[] args) { try { Prepare(); Thread t1 = new Thread(Program.Run); t1.Start(); t1.Join(); Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message + "\t" + ex.StackTrace); } } } thanks in avdance, George

    Read the article

  • NHibernate Many-to-Many Mapping not working

    - by ClutchDude
    I have a Nhibernate mapping file for a simple user/role mapping. Here are the mapping files: Users.hbm.xml <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Sample.Persistence" namespace="Sample.Persistence.Model"> <class name="User" table="Users"> <id name="UserKey"> <generator class="identity"/> </id> <property name="UserName" column="UserName" type="String" /> <property name="Password" column="Password" type="Byte[]" /> <property name="FirstName" column="FirstName" type="String" /> <property name="LastName" column="LastName" type="String" /> <property name="Email" column="Email" type="String" /> <property name="Active" column="Active" type="Boolean" /> <property name="Locked" column="Locked" type="Boolean" /> <property name="LoginFailures" column="LoginFailures" type="int" /> <property name="LockoutDate" column="LockoutDate" type="DateTime" generated="insert" /> <property name="Expired" column="Expired" type="Boolean" generated="insert"/> <set name="Roles" table="UsersRolesBridge" lazy="false"> <key column="UserKey" /> <many-to-many class="Role" not-found="exception" column="RoleKey" /> </set> </class> </hibernate-mapping> Role.hbm.xml <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Sample.Persistence" namespace="Sample.Persistence.Model"> <class name="Role" table="Roles"> <id name="RoleKey"> <generator class="identity"/> </id> <property name="Name" column="Name" type="String" /> <set name="Users" inverse="true" atable="UsersRolesBridge" lazy="false" > <key column="RoleKey" /> <many-to-many class="User" column="UserKey" /> </set> </class> </hibernate-mapping> I am able to retrieve roles for each user via NHibernate but when I go to save a new object, the roles are not saved in the Bridge table. The user is created and insert with no issues. I've checked that the Role collection, a field on the user, is being populated with the proper rolekey before the Session.Save() is called. There is no exception thrown as well.

    Read the article

  • ASP Function that returns result from stored procedures

    - by Brad
    I am working on a project that requires me to hop into to separate DB's. So I have figured that I need to have multiple functions inside of my VB page. The only problem I am having,is I am not to sure how to get this all accomplished. So far I have figured out the overall structure, just need help implementing that structure. Here is my idea: The main Function would call two other functions. We can Call them Sub Function 1 and Sub Function 2. So, the main Function takes the saved sessions information for the E-mail address and dumps in into Sub Function 1. It needs to open up a new connection to the db/stored procedure and RUN the following procedure and then return the result. Here is the stored procedure and what i think is correct. CREATE PROCEDURE WEB_User ( @EMAIL_ADDRESS varchar(80) = [EMAIL_ADDRESS] ) AS SELECT MEMBER_NUMBER FROM WEB_LOGIN WHERE EMAIL_ADDRESS = @EMAIL_ADDRESS So my question is, what is the function suppose to look like? how do I send the session information to the procedure? and finally, how do I return the stored procedure results and push back into the main function so it can be carried into sub function 2? Thank you in advance for your help... I really appreciate it!

    Read the article

  • How can I make a multi search SPROC/UDF by passing a tabled-value to it?

    - by Shimmy
    I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of that item xml element> --Or in other words: SELECT FROM Items WHERE Item IN XML according to the splecified columns. Am I clear enought? I don't mind to do it in a different way other than xml. What I need is selecting values that mach an array of two of its columns' values.

    Read the article

  • Will this SQL screw up

    - by Joshua
    I'm sure everyone knows the joys of concurrency when it comes to threading. Imagine the following scenario on every page-load on a noobily set up MySQL db: UPDATE stats SET visits = (visits+1) If a thousand users load the page at same time, will the count screw up? is this that table locking/row locking crap? Which one mysql use.

    Read the article

  • How to get count of another table in a left join

    - by Sinan
    I have multiple tables post id Name 1 post-name1 2 post-name2 user id username 1 user1 2 user2 post_user post_id user_id 1 1 2 1 post_comments post_id comment_id 1 1 1 2 1 3 I am using a query like this: SELECT post.id, post.title, user.id AS uid, username FROM `post` LEFT JOIN post_user ON post.id = post_user.post_id LEFT JOIN user ON user.id = post_user.user_id ORDER BY post_date DESC It works as intended. However I would like the get the number of comments for each post too. So how can i modify the this query so I can get the count of comments. Any ideas?

    Read the article

  • SQLServer using too much memory

    - by Israel Pereira Valverde
    I have installed on my desktop machine (with windows 7) SQLServer 2008 R2 Express. I have only one local server running (./SQLEXPRESS) but the sqlserver process is taking ALL the RAM possible. With an machine with 3GB of RAM the things starts to get slow, so I limited the maximun amount of RAM in the server, and now, constantly the SQLServer give some error messages that the memory is not enought. It's using 1GB of RAM with only one LOCAL server with 2 databases completely empty, how 1GB of RAM isn't enought ? When the process start it's using an really acceptable amount of memory (around 80MB) but it's keep increasing until it reaches the maximun defined and start to complain about having not enought memory available. In that point I have to restart the server to use it again. I have read about an hotfix to solve one of the errors I got from sqlserver: There is insufficient system memory in resource pool 'internal' to run this query But it's already installed on my sqlserver. Why it's using so much memory?

    Read the article

  • Mysql - help me optimize this query (improved question)

    - by sandeepan-nath
    About the system: - There are tutors who create classes and packs - A tags based search approach is being followed.Tag relations are created when new tutors register and when tutors create packs (this makes tutors and packs searcheable). For details please check the section How tags work in this system? below. Following is the concerned query SELECT SUM(DISTINCT( t.tag LIKE "%Dictatorship%" )) AS key_1_total_matches, SUM(DISTINCT( t.tag LIKE "%democracy%" )) AS key_2_total_matches, COUNT(DISTINCT( od.id_od )) AS tutor_popularity, CASE WHEN ( IF(( wc.id_wc > 0 ), ( wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date > '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'classes_published', CASE WHEN ( IF(( lp.id_lp > 0 ), ( lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ), 0) ) THEN 1 ELSE 0 END AS 'packs_published', td . *, u . * FROM tutor_details AS td JOIN users AS u ON u.id_user = td.id_user LEFT JOIN learning_packs_tag_relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor LEFT JOIN learning_packs AS lp ON lptagrels.id_lp = lp.id_lp LEFT JOIN learning_packs_categories AS lpc ON lpc.id_lp_cat = lp.id_lp_cat LEFT JOIN learning_packs_categories AS lpcp ON lpcp.id_lp_cat = lpc.id_parent LEFT JOIN learning_pack_content AS lpct ON ( lp.id_lp = lpct.id_lp ) LEFT JOIN webclasses_tag_relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN webclasses AS wc ON wtagrels.id_wc = wc.id_wc LEFT JOIN learning_packs_categories AS wcc ON wcc.id_lp_cat = wc.id_wp_cat LEFT JOIN learning_packs_categories AS wccp ON wccp.id_lp_cat = wcc.id_parent LEFT JOIN order_details AS od ON td.id_tutor = od.id_author LEFT JOIN orders AS o ON od.id_order = o.id_order LEFT JOIN tutors_tag_relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor JOIN tags AS t ON ( t.id_tag = ttagrels.id_tag ) OR ( t.id_tag = lptagrels.id_tag ) OR ( t.id_tag = wtagrels.id_tag ) WHERE ( u.country = 'IE' OR u.country IN ( 'INT' ) ) AND CASE WHEN ( ( t.id_tag = lptagrels.id_tag ) AND ( lp.id_lp 0 ) ) THEN lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND ( lpcp.country_code = 'IE' OR lpcp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( ( t.id_tag = wtagrels.id_tag ) AND ( wc.id_wc 0 ) ) THEN wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date '2010-06-01 22:00:56' AND wccp.status = 1 AND ( wccp.country_code = 'IE' OR wccp.country_code IN ( 'INT' ) ) ELSE 1 END AND CASE WHEN ( od.id_od 0 ) THEN od.id_author = td.id_tutor AND o.order_status = 'paid' AND CASE WHEN ( od.id_wc 0 ) THEN od.can_attend_class = 1 ELSE 1 END ELSE 1 END GROUP BY td.id_tutor HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 ORDER BY tutor_popularity DESC, u.surname ASC, u.name ASC LIMIT 0, 20 The problem The results returned by the above query are correct (AND logic working as per expectation), but the time taken by the query rises alarmingly for heavier data and for the current data I have it is like 25 seconds as against normal query timings of the order of 0.005 - 0.0002 seconds, which makes it totally unusable. It is possible that some of the delay is being caused because all the possible fields have not yet been indexed. The tag field of tags table is indexed. Is there something faulty with the query? What can be the reason behind 20+ seconds of execution time? How tags work in this system? When a tutor registers, tags are entered and tag relations are created with respect to tutor's details like name, surname etc. When a Tutors create packs, again tags are entered and tag relations are created with respect to pack's details like pack name, description etc. tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All individual tags are stored in tags table. The explain query output:- Please see this screenshot - http://www.test.examvillage.com/Explain_query.jpg

    Read the article

  • SQLite - ON DUPLICATE KEY UPDATE

    - by Alix Axel
    MySQL has something like this: INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1; As far as I'm know this feature doesn't exist in SQLite, what I want to know is if there is any way to archive the same effect without having to execute two queries. Also, if this is not possible, what do you prefer: SELECT + (INSERT or UPDATE) or UPDATE (+ INSERT if UPDATE fails)

    Read the article

  • How to generate a transaction number?

    - by 001
    I was thinking of formatting it like this TYYYYMMDDNNNNNNNNNNX (1 character + 19 digits) Where T is type YYYY is year MM is month DD is day N is sequencial number X is check digit The problem is, how do I generate the sequencial number? since my primary key is not an auto increment integer value, if it was i would use that, but its not. EDIT can I have the sequencial number resets itself after 1 day (24hours). P201012080000000001X <-- first transaction of 2010/12/08 P2010120810000000002X <--- second transaction of 2010/12/08 P201012090000000001X <--- First transaction of 2010/12/09 (X is the check digit)

    Read the article

  • get output of last Process on SSAS cube

    - by Raj More
    I have processed a SSAS cube. After it was done processing, I hit the close button - and then realized that I should have saved the output. I think SSAS stores the processing log as a text or XML file, but I do not know what folder to look into. Can someone direct me to retrieving processing logs?

    Read the article

  • Retrieving Top 10 rows ans sum all others in row 11

    - by Mario
    Hello all, I have the following query that retrieve the number of users per country; SELECT C.CountryID AS CountryID, C.CountryName AS Country, Count(FirstName) AS Origin FROM Users AS U INNER JOIN Country AS C ON C.CountryID = U.CountryOfOrgin GROUP BY CASE C.CountryName, C.CountryID What I need is a way to get the top 10 and then sum all other users in a single row. I know how to get the top 10 but I`m stuck on getting the remaining in a single row. Is there a simple way to do it? For example if the above query returns 17 records the top ten are displayed and a sum of the users from the 7 remaining country should appear on row 11. On that row 11 the countryid would be 0 and countryname Others Thanks for your help!

    Read the article

  • User has many computers, computers have many attributes in different tables, best way to JOIN?

    - by krismeld
    I have a table for users: USERS: ID | NAME | ---------------- 1 | JOHN | 2 | STEVE | a table for computers: COMPUTERS: ID | USER_ID | ------------------ 13 | 1 | 14 | 1 | a table for processors: PROCESSORS: ID | NAME | --------------------------- 27 | PROCESSOR TYPE 1 | 28 | PROCESSOR TYPE 2 | and a table for harddrives: HARDDRIVES: ID | NAME | ---------------------------| 35 | HARDDRIVE TYPE 25 | 36 | HARDDRIVE TYPE 90 | Each computer can have many attributes from the different attributes tables (processors, harddrives etc), so I have intersection tables like this, to link the attributes to the computers: COMPUTER_PROCESSORS: C_ID | P_ID | --------------| 13 | 27 | 13 | 28 | 14 | 27 | COMPUTER_HARDDRIVES: C_ID | H_ID | --------------| 13 | 35 | So user JOHN, with id 1 owns computer 13 and 14. Computer 13 has processor 27 and 28, and computer 13 has harddrive 35. Computer 14 has processor 27 and no harddrive. Given a user's id, I would like to retrieve a list of that user's computers with each computers attributes. I have figured out a query that gives me a somewhat of a result: SELECT computers.id, processors.id AS p_id, processors.name AS p_name, harddrives.id AS h_id, harddrives.name AS h_name, FROM computers JOIN computer_processors ON (computer_processors.c_id = computers.id) JOIN processors ON (processors.id = computer_processors.p_id) JOIN computer_harddrives ON (computer_harddrives.c_id = computers.id) JOIN harddrives ON (harddrives.id = computer_harddrives.h_id) WHERE computers.user_id = 1 Result: ID | P_ID | P_NAME | H_ID | H_NAME | ----------------------------------------------------------- 13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 | 13 | 28 | PROCESSOR TYPE 2 | 35 | HARDDRIVE TYPE 25 | But this has several problems... Computer 14 doesnt show up, because it has no harddrive. Can I somehow make an OUTER JOIN to make sure that all computers show up, even if there a some attributes they don't have? Computer 13 shows up twice, with the same harddrive listet for both. When more attributes are added to a computer (like 3 blocks of ram), the number of rows returned for that computer gets pretty big, and it makes it had to sort the result out in application code. Can I somehow make a query, that groups the two returned rows together? Or a query that returns NULL in the h_name column in the second row, so that all values returned are unique? EDIT: What I would like to return is something like this: ID | P_ID | P_NAME | H_ID | H_NAME | ----------------------------------------------------------- 13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 | 13 | 28 | PROCESSOR TYPE 2 | 35 | NULL | 14 | 27 | PROCESSOR TYPE 1 | NULL | NULL | Or whatever result that make it easy to turn it into an array like this [13] => [P_NAME] => [0] => PROCESSOR TYPE 1 [1] => PROCESSOR TYPE 2 [H_NAME] => [0] => HARDDRIVE TYPE 25 [14] => [P_NAME] => [0] => PROCESSOR TYPE 1

    Read the article

  • How to return table name from stored procedure in dataset.

    - by Shantanu Gupta
    I used a dataset to store 15 tables that I need at the time of loading. When i filled all the tables using stored procedure it returns me all the table but name of the table doesn't comes as that of actual table name in a database. It takes all the table with table name as Table1, Table2, Table3... I want them to be with the name as they actually are in table. SELECT PK_GUEST_TYPE, [DESCRIPTION] FROM L_GUEST_TYPE SELECT PK_AGE_GROUP_ID, AGE_GROUP FROM L_AGE_GROUP SELECT PK_COMPANY_ID, COMPANY_NAME FROM M_COMPANY SELECT PK_COUNTRY_ID, COUNTRY FROM L_COUNTRY SELECT PK_EYE_COLOR_ID, [DESCRIPTION] FROM L_EYE_COLOR SELECT PK_GENDER_ID, [DESCRIPTION] FROM L_GENDER SELECT PK_HAIR_COLOR_ID, [DESCRIPTION] FROM L_HAIR_COLOR SELECT PK_STATE_PROVONCE_ID, [DESCRIPTION] FROM L_STATE_PROVINCE SELECT PK_STATUS_ID, [DESCRIPTION] FROM L_STATUS SELECT PK_TITLE_ID, [DESCRIPTION] FROM L_TITLE SELECT PK_TOWER_ID, [DESCRIPTION] FROM M_TOWER SELECT PK_CITY_ID, [DESCRIPTION] FROM L_CITY SELECT PK_REGISTER_TYPE_ID, [DESCRIPTION] FROM L_REGISTER_TYPE Here is my frontend coding to fill dataset. OpenConnection(); adp.Fill(ds); CloseConnection(true);

    Read the article

  • How can I combine result and subquery for IN comparison (mysql)

    - by user325804
    In order for a school project i need to create the following situation within one mysql query. The situation is as such, that a child's tags and a parent's tags need to be combined into one, and compared to a site's tags, depending on a few extra simple equals to lines. For this to happen I only see the option that the result of a subquery is combined with a sub query within that query, as such: SELECT tag.*, (SELECT group_concat(t1.id, ',', (SELECT group_concat(tag.id) FROM adcampaign INNER JOIN adcampaign_tag ON adcampaign.id = adcampaign_tag.adcampaign_id INNER JOIN tag ON adcampaign_tag.tag_id = tag.id WHERE adcampaign.id = 1)) FROM ad, ad_tag, tag AS t1 WHERE ad.id = ad_tag.ad_id AND ad_tag.tag_id = t1.id AND ad.adcampaign_id = 1 AND ad.agecategory_id = 1 AND ad.adsize_id = 1 AND ad.adtype_id = 1) as tags FROM tag WHERE tag.id IN tags But the IN comparison only returns the first result because now the tags aren't a list but a concanated string. Anyone got any suggestion on this? I really need a way to combine it into one array

    Read the article

  • select from multiple tables but ordering by a datetime field

    - by Chris Mccabe
    I have 3 tables that are unrelated (related that each contains data for a different social network). Each has a datetime field dated- I'm already grouping by hour as you can see below (this one below for linked_in) SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour FROM upd8r_linked_in_accts WHERE CAST(dated AS DATE) = '".$start_date."' GROUP BY hour I would like to know how to do a total across all 3 networks- the tables for the three are CREATE TABLE IF NOT EXISTS `upd8r_facebook_accts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` varchar(50) NOT NULL, `user_id` int(11) NOT NULL, `fb_id` bigint(30) NOT NULL, `dated` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=80 ; CREATE TABLE IF NOT EXISTS `upd8r_linked_in_accts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` varchar(50) NOT NULL, `user_id` int(11) NOT NULL, `linked_in` varchar(200) NOT NULL, `oauth_secret` varchar(100) NOT NULL, `first_count` int(11) NOT NULL, `second_count` int(11) NOT NULL, `dated` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=200 ; CREATE TABLE IF NOT EXISTS `upd8r_twitter_accts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` varchar(50) NOT NULL, `user_id` int(11) NOT NULL, `twitter` varchar(200) NOT NULL, `twitter_secret` varchar(100) NOT NULL, `dated` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; something like this ? (SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour FROM upd8r_linked_in_accts WHERE CAST(dated AS DATE) = '".$start_date."') UNION ALL (SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour FROM upd8r_facebook_accts WHERE CAST(dated AS DATE) = '".$start_date."') UNION ALL (SELECT count(*), date_format(dated, '%Y:%m:%d %H') as hour FROM upd8r_twitter_accts WHERE CAST(dated AS DATE) = '".$start_date."') UNION ALL GROUP BY hour

    Read the article

  • Login Problem Windows Authentication

    - by user109280
    Duplicate of: http://stackoverflow.com/questions/881928/windows-authentication-trusted-connection-problem I logged in the Windows Server(Machine 1) as "abc\user1 ". Windows Server machine is in abc domain. MSSQL Server is in the "abc" domain on Machine 1 and have mixed mode.authentication. It has account "abc\user1 " and "abc\user2 ". Both has role of sysadmin and serveradmin. I logged in another machine(Machine 2) using "abc\user2 ". Same Domain. Run the ant which connect to MSSQL Server. URL is formed as follows. jdbc:sqlserver://%DB_IP%:%DB_PORT%;SelectMethod=cursor;integratedSecurity=true;DatabaseName=dbname; 1) From Machine 2, If I use "abc\user2" credential for connection, then it works fine. since integratedSecurity=true. 2) From Machine 2, If I use "abc\user1" credential for connection, then it doesn't fine, since integratedSecurity=true and take System Credentials i.e "abc\user2". Even if I make integratedSecurity=false , then also it doesn't connect using "abc\user1" What changes to URL I have make to work for "abc\user1" from Machine2 for connection. what properties to be added in url? OR Driver doesn't support to use another domain\User Credentials? What need to set on MSSQL Server ?? Deepak

    Read the article

  • Why is SQLite3 using covering indices instead of the indices I created?

    - by Geoff
    I have an extremely large database (contacts has ~3 billion entries, people has ~280 million entries, and the other tables have a negligible number of entries). Most other queries I've run are really fast. However, I've encountered a more complicated query that's really slow. I'm wondering if there's any way to speed this up. First of all, here is my schema: CREATE TABLE activities (id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE contacts ( id INTEGER PRIMARY KEY, person1_id INTEGER NOT NULL, person2_id INTEGER NOT NULL, duration REAL NOT NULL, -- hours activity_id INTEGER NOT NULL -- FOREIGN_KEY(person1_id) REFERENCES people(id), -- FOREIGN_KEY(person2_id) REFERENCES people(id) ); CREATE TABLE people ( id INTEGER PRIMARY KEY, state_id INTEGER NOT NULL, county_id INTEGER NOT NULL, age INTEGER NOT NULL, gender TEXT NOT NULL, -- M or F income INTEGER NOT NULL -- FOREIGN_KEY(state_id) REFERENCES states(id) ); CREATE TABLE states ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, abbreviation TEXT NOT NULL ); CREATE INDEX activities_name_index on activities(name); CREATE INDEX contacts_activity_id_index on contacts(activity_id); CREATE INDEX contacts_duration_index on contacts(duration); CREATE INDEX contacts_person1_id_index on contacts(person1_id); CREATE INDEX contacts_person2_id_index on contacts(person2_id); CREATE INDEX people_age_index on people(age); CREATE INDEX people_county_id_index on people(county_id); CREATE INDEX people_gender_index on people(gender); CREATE INDEX people_income_index on people(income); CREATE INDEX people_state_id_index on people(state_id); CREATE INDEX states_abbreviation_index on states(abbreviation); CREATE INDEX states_name_index on states(name); Note that I've created an index on every column in the database. I don't care about the size of the database; speed is all I care about. Here's an example of a query that, as expected, runs almost instantly: SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; Here's the troublesome query: SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); Now, what I think would happen is that each subquery would use each relevant index I've created to speed this up. However, when I show the query plan, I see this: sqlite> EXPLAIN QUERY PLAN SELECT * FROM contacts WHERE rowid IN (SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person1_id=people.id AND people.state_id=states.id AND states.name='Kansas' INTERSECT SELECT contacts.rowid FROM contacts, people, states WHERE contacts.person2_id=people.id AND people.state_id=states.id AND states.name='Missouri'); 0|0|0|SEARCH TABLE contacts USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 2|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 2|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 2|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person1_id_index (person1_id=?) (~12 rows) 3|0|2|SEARCH TABLE states USING COVERING INDEX states_name_index (name=?) (~1 rows) 3|1|1|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) 3|2|0|SEARCH TABLE contacts USING COVERING INDEX contacts_person2_id_index (person2_id=?) (~12 rows) 1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT) In fact, if I show the query plan for the first query I posted, I get this: sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM people, states WHERE people.state_id=states.id and states.abbreviation='IA'; 0|0|1|SEARCH TABLE states USING COVERING INDEX states_abbreviation_index (abbreviation=?) (~1 rows) 0|1|0|SEARCH TABLE people USING COVERING INDEX people_state_id_index (state_id=?) (~5569556 rows) Why is SQLite using covering indices instead of the indices I created? Shouldn't the search in the people table be able to happen in log(n) time given state_id which in turn is found in log(n) time?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 707 708 709 710 711 712 713 714 715 716 717 718  | Next Page >