Search Results

Search found 38660 results on 1547 pages for 'sql index'.

Page 604/1547 | < Previous Page | 600 601 602 603 604 605 606 607 608 609 610 611  | Next Page >

• MySql query optimization help

  - by rohitgu

  I have few queries and am not able to figure out how to optimize them, QUERY 1 select * from t_twitter_tracking where classified is null and tweetType='ENGLISH' order by id limit 500; QUERY 2 Select count(*) as cnt, DATE_FORMAT(CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00','+05:30'),'%Y-%m-%d') as dat from t_twitter_tracking wrdTrk where wrdTrk.word like ('dell') and CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00','+05:30') between '2010-12-12 00:00:00' and '2010-12-26 00:00:00' group by dat; Both these queries run on the same table, CREATE TABLE `t_twitter_tracking` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `word` VARCHAR(200) NOT NULL, `tweetId` BIGINT(100) NOT NULL, `twtText` VARCHAR(800) NULL DEFAULT NULL, `language` TEXT NULL, `links` TEXT NULL, `tweetType` VARCHAR(20) NULL DEFAULT NULL, `source` TEXT NULL, `sourceStripped` TEXT NULL, `isTruncated` VARCHAR(40) NULL DEFAULT NULL, `inReplyToStatusId` BIGINT(30) NULL DEFAULT NULL, `inReplyToUserId` INT(11) NULL DEFAULT NULL, `rtUsrProfilePicUrl` TEXT NULL, `isFavorited` VARCHAR(40) NULL DEFAULT NULL, `inReplyToScreenName` VARCHAR(40) NULL DEFAULT NULL, `latitude` BIGINT(100) NOT NULL, `longitude` BIGINT(100) NOT NULL, `retweetedStatus` VARCHAR(40) NULL DEFAULT NULL, `statusInReplyToStatusId` BIGINT(100) NOT NULL, `statusInReplyToUserId` BIGINT(100) NOT NULL, `statusFavorited` VARCHAR(40) NULL DEFAULT NULL, `statusInReplyToScreenName` TEXT NULL, `screenName` TEXT NULL, `profilePicUrl` TEXT NULL, `twitterId` BIGINT(100) NOT NULL, `name` TEXT NULL, `location` VARCHAR(100) NULL DEFAULT NULL, `bio` TEXT NULL, `url` TEXT NULL COLLATE 'latin1_swedish_ci', `utcOffset` INT(11) NULL DEFAULT NULL, `timeZone` VARCHAR(100) NULL DEFAULT NULL, `frenCnt` BIGINT(20) NULL DEFAULT '0', `createdAt` DATETIME NULL DEFAULT NULL, `createdOnGMT` VARCHAR(40) NULL DEFAULT NULL, `createdOnServerTime` DATETIME NULL DEFAULT NULL, `follCnt` BIGINT(20) NULL DEFAULT '0', `favCnt` BIGINT(20) NULL DEFAULT '0', `totStatusCnt` BIGINT(20) NULL DEFAULT NULL, `usrCrtDate` VARCHAR(200) NULL DEFAULT NULL, `humanSentiment` VARCHAR(30) NULL DEFAULT NULL, `replied` BIT(1) NULL DEFAULT NULL, `replyMsg` TEXT NULL, `classified` INT(32) NULL DEFAULT NULL, `createdOnGMTDate` DATETIME NULL DEFAULT NULL, `locationDetail` TEXT NULL, `geonameid` INT(11) NULL DEFAULT NULL, `country` VARCHAR(255) NULL DEFAULT NULL, `continent` CHAR(2) NULL DEFAULT NULL, `placeLongitude` FLOAT NULL DEFAULT NULL, `placeLatitude` FLOAT NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `id` (`id`, `word`), INDEX `createdOnGMT_index` (`createdOnGMT`) USING BTREE, INDEX `word_index` (`word`) USING BTREE, INDEX `location_index` (`location`) USING BTREE, INDEX `classified_index` (`classified`) USING BTREE, INDEX `tweetType_index` (`tweetType`) USING BTREE, INDEX `getunclassified_index` (`classified`, `tweetType`) USING BTREE, INDEX `timeline_index` (`word`, `createdOnGMTDate`, `classified`) USING BTREE, INDEX `createdOnGMTDate_index` (`createdOnGMTDate`) USING BTREE, INDEX `locdetail_index` (`country`, `id`) USING BTREE, FULLTEXT INDEX `twtText_index` (`twtText`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DEFAULT AUTO_INCREMENT=12608048; The table has more than 10 million records. How can I optimize it?

  Read the article

• Primary Key Identity Value Increments On Unique Key Constraint Violation

  - by Jed

  I have a SqlServer 2008 table which has a Primary Key (IsIdentity=Yes) and three other fields that make up a Unique Key constraint. In addition I have a store procedure that inserts a record into the table and I call the sproc via C# using a SqlConnection object. The C# sproc call works fine, however I have noticed interesting results when the C# sproc call violates the Unique Key constraint.... When the sproc call violates the Unique Key constraint, a SqlException is thrown - which is no surprise and cool. However, I notice that the next record that is successfully added to the table has a PK value that is not exactly one more than the previous record - For example: Say the table has five records where the PK values are 1,2,3,4, and 5. The sproc attempts to insert a sixth record, but the Unique Key constraint is violated and, so, the sixth record is not inserted. Then the sproc attempts to insert another record and this time it is successful. - This new record is given a PK value of 7 instead of 6. Is this normal behavior? If so, can you give me a reason why this is so? (If a record fails to insert, why is the PK index incremented?) If this is not normal behavior, can you give me any hints as to why I am seeing these symptoms?

  Read the article

• An unhandled exception of type 'System.StackOverflowException' occurred in mscorlib.dll

  - by mazhar

  Ok the thing is that I am using asp.net mvc with linq to sql. I have a scenario where there are two tables group and features with many to many relationship with the third table groupfeatures. I have drag and drop all three table in the dbml file. The thing is that it runs fine with group but when I call the Feature things the above error occured and the compilers stops at this line in ((())). public partial class EgovtDataContext : System.Data.Linq.DataContext { private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource(); #region Extensibility Method Definitions partial void OnCreated(); partial void InsertGroup(Group instance); partial void UpdateGroup(Group instance); partial void DeleteGroup(Group instance); partial void InsertFeature(Feature instance); partial void UpdateFeature(Feature instance); partial void DeleteFeature(Feature instance); partial void InsertGroupFeature(GroupFeature instance); partial void UpdateGroupFeature(GroupFeature instance); partial void DeleteGroupFeature(GroupFeature instance); #endregion (((public EgovtDataContext() : base(global::System.Configuration.ConfigurationManager.ConnectionStrings["egovtsConnectionString"].ConnectionString, mappingSource)))) The code in both the controller Group and features file is ditto copied. Note the group things are working, the features things are not. FeaturesRepository.cs public IQueryable<Feature> FindAllFeatures() { return db.Features; } FeaturesController.cs FeaturesRepository FeatureRepository = new FeaturesRepository(); public ActionResult Index() { var Feature = FeatureRepository.FindAllFeatures(); return View(Feature); } So what could be wrong?

  Read the article

• What purpose does “using” serve when used the following way

  - by user287745

  What purpose does “using” serve when used the following way:- ONE EXAMPLE IS THIS, (AN ANSWERER- @richj - USED THIS CODE TO SOLVE A PROBLEM THANKS) private Method(SqlConnection connection) { using (SqlTransaction transaction = connection.BeginTransaction()) { try { // Use the connection here .... transaction.Commit(); } catch { transaction.Rollback(); throw; } } } OTHER EXAMPLE I FOUND WHILE READING ON MICROSOFT SUPPORT SITE public static void ShowSqlException(string connectionString) { string queryString = "EXECUTE NonExistantStoredProcedure"; StringBuilder errorMessages = new StringBuilder(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); try { command.Connection.Open(); command.ExecuteNonQuery(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } Console.WriteLine(errorMessages.ToString()); } } } I AM doing at top of page as using system.data.sqlclient etc so why this using thing in middle of code, What if I omit it (I know the code will work) but what functionality will I be losing

  Read the article

• mysql error in php

  - by fusion

  i'm trying to run this php code which should display a quote from mysql, but can't figure out where is it going wrong. the result variable is null or empty. can someone help me out. thanks! <?php include 'config.php'; // 'text' is the name of your table that contains // the information you want to pull from $rowcount = mysql_query("select count(*) as rows from quotes"); // Gets the total number of items pulled from database. while ($row = mysql_fetch_assoc($rowcount)) { $max = $row["rows"]; //print_r ($max); } // Selects an item's index at random $rand = rand(1,$max)-1; print_r ($rand); $result = mysql_query("select * from quotes limit $rand, 1") or die ('Error: '.mysql_error()); if (!$result or mysql_num_rows($result)) { echo "Empty"; } else{ while ($row = mysql_fetch_array($result)) { $randomOutput = $row['cQuotes']; echo '<p>' . $randomOutput . '</p>'; } }

  Read the article

• SqlDataAdaptor why get a different query

  - by Murat

  Hi All, I have a database reader class. But i want to use this class, sometimes SqlDataAdaptor.Fill() method get another query to Datatable. I look at Query in SqlCommand Instance and my query is correct but returning values from different table? What is the problem? This My Code public static DataTable GetLatestRecords(string TableName, string FilterFieldName, int RecordCount, params DBFieldAndValue[] FilterFields) { DataTable DT = new DataTable(); Type ClassType = typeof(T); string SelectString = string.Format("SELECT TOP ({0}) * FROM {1}", RecordCount, TableName); if (FilterFields.Length > 0) { SelectString += " WHERE 1 = 1 "; for (int index = 0; index < FilterFields.Length; index++) SelectString += FilterFields[index].ToString(index); } SelectString += string.Format(" ORDER BY {0} DESC", FilterFieldName); try { SqlConnection Connection = GetConnection<T>(); SqlCommand Command = new SqlCommand(SelectString, Connection); for (int index = 0; index < FilterFields.Length; index++) Command.Parameters.AddWithValue("@" + FilterFields[index].Field + "_" + index, FilterFields[index].Value); if (Connection.State == ConnectionState.Closed) Connection.Open(); Command.CommandText = SelectString; SqlDataAdapter DA = new SqlDataAdapter(Command); DT.Dispose(); DT = new DataTable(); DA.Fill(DT); if (Connection.State == ConnectionState.Open) Connection.Close(); } catch (Exception ex) { WriteLogStatic(ex.Message); throw new Exception(@"Veritabani islemleri sirasinda hata olustu!\nAyrintilar 'C:\Temp' dizini altindadir.\n" + ex.Message); } return DT; }

  Read the article

• PHP/SQL/Wordpress: Group a user list by alphabet

  - by rayne

  I want to create a (fairly big) Wordpress user index with the users categorized alphabetically, like this: A Amy Adam B Bernard Bianca and so on. I've created a custom Wordpress query which works fine for this, except for one problem: It also displays "empty" letters, letters where there aren't any users whose name begins with that letter. I'd be glad if you could help me fix this code so that it only displays the letter if there's actually a user with a name of that letter :) I've tried my luck by checking how many results there are for that letter, but somehow that's not working. (FYI, I use the user photo plugin and only want to show users in the list who have an approved picture, hence the stuff in the SQL query). <?php $alphabet = range('A', 'Z'); foreach ($alphabet as $letter) { $user_count = $wpdb->get_results("SELECT COUNT(*) FROM wp_users WHERE display_name LIKE '".$letter."%' ORDER BY display_name ASC"); if ($user_count > 0) { $user_row = $wpdb->get_results("SELECT wp_users.user_login, wp_users.display_name FROM wp_users, wp_usermeta WHERE wp_users.display_name LIKE '".$letter."%' AND wp_usermeta.meta_key = 'userphoto_approvalstatus' AND wp_usermeta.meta_value = '2' AND wp_usermeta.user_id = wp_users.ID ORDER BY wp_users.display_name ASC"); echo '<li class="letter">'.$letter.''; echo '<ul>'; foreach ($user_row as $user) { echo '<li><a href="/author/'.$user->user_login.'">'.$user->display_name.'</a></li>'; } echo '</ul></li>'; } } ?> Thanks in advance!

  Read the article

• asp.net mvc insert doesnt seem to work for me....

  - by Pandiya Chendur

  My controller's call to repository insert method all the values are passed but it doesn't get inserted in my table.. My controller method, [AcceptVerbs(HttpVerbs.Post)] public ActionResult Create([Bind(Exclude = "Id")]FormCollection collection) { try { MaterialsObj materialsObj = new MaterialsObj(); materialsObj.Mat_Name = collection["Mat_Name"]; materialsObj.Mes_Id = Convert.ToInt64(collection["MeasurementType"]); materialsObj.Mes_Name = collection["Mat_Type"]; materialsObj.CreatedDate = System.DateTime.Now; materialsObj.CreatedBy = Convert.ToInt64(1); materialsObj.IsDeleted = Convert.ToInt64(1); consRepository.createMaterials(materialsObj); return RedirectToAction("Index"); } catch { return View(); } } and my repository, public MaterialsObj createMaterials(MaterialsObj materialsObj) { Material mat = new Material(); mat.Mat_Name = materialsObj.Mat_Name; mat.Mat_Type = materialsObj.Mes_Name; mat.MeasurementTypeId = materialsObj.Mes_Id; mat.Created_Date = materialsObj.CreatedDate; mat.Created_By = materialsObj.CreatedBy; mat.Is_Deleted = materialsObj.IsDeleted; db.Materials.InsertOnSubmit(mat); return materialsObj; } What am i missing here any suggestion....

  Read the article

• Python performance improvement request for winkler

  - by Martlark

  I'm a python n00b and I'd like some suggestions on how to improve the algorithm to improve the performance of this method to compute the Jaro-Winkler distance of two names. def winklerCompareP(str1, str2): """Return approximate string comparator measure (between 0.0 and 1.0) USAGE: score = winkler(str1, str2) ARGUMENTS: str1 The first string str2 The second string DESCRIPTION: As described in 'An Application of the Fellegi-Sunter Model of Record Linkage to the 1990 U.S. Decennial Census' by William E. Winkler and Yves Thibaudeau. Based on the 'jaro' string comparator, but modifies it according to whether the first few characters are the same or not. """ # Quick check if the strings are the same - - - - - - - - - - - - - - - - - - # jaro_winkler_marker_char = chr(1) if (str1 == str2): return 1.0 len1 = len(str1) len2 = len(str2) halflen = max(len1,len2) / 2 - 1 ass1 = '' # Characters assigned in str1 ass2 = '' # Characters assigned in str2 #ass1 = '' #ass2 = '' workstr1 = str1 workstr2 = str2 common1 = 0 # Number of common characters common2 = 0 #print "'len1', str1[i], start, end, index, ass1, workstr2, common1" # Analyse the first string - - - - - - - - - - - - - - - - - - - - - - - - - # for i in range(len1): start = max(0,i-halflen) end = min(i+halflen+1,len2) index = workstr2.find(str1[i],start,end) #print 'len1', str1[i], start, end, index, ass1, workstr2, common1 if (index > -1): # Found common character common1 += 1 #ass1 += str1[i] ass1 = ass1 + str1[i] workstr2 = workstr2[:index]+jaro_winkler_marker_char+workstr2[index+1:] #print "str1 analyse result", ass1, common1 #print "str1 analyse result", ass1, common1 # Analyse the second string - - - - - - - - - - - - - - - - - - - - - - - - - # for i in range(len2): start = max(0,i-halflen) end = min(i+halflen+1,len1) index = workstr1.find(str2[i],start,end) #print 'len2', str2[i], start, end, index, ass1, workstr1, common2 if (index > -1): # Found common character common2 += 1 #ass2 += str2[i] ass2 = ass2 + str2[i] workstr1 = workstr1[:index]+jaro_winkler_marker_char+workstr1[index+1:] if (common1 != common2): print('Winkler: Wrong common values for strings "%s" and "%s"' % \ (str1, str2) + ', common1: %i, common2: %i' % (common1, common2) + \ ', common should be the same.') common1 = float(common1+common2) / 2.0 ##### This is just a fix ##### if (common1 == 0): return 0.0 # Compute number of transpositions - - - - - - - - - - - - - - - - - - - - - # transposition = 0 for i in range(len(ass1)): if (ass1[i] != ass2[i]): transposition += 1 transposition = transposition / 2.0 # Now compute how many characters are common at beginning - - - - - - - - - - # minlen = min(len1,len2) for same in range(minlen+1): if (str1[:same] != str2[:same]): break same -= 1 if (same > 4): same = 4 common1 = float(common1) w = 1./3.*(common1 / float(len1) + common1 / float(len2) + (common1-transposition) / common1) wn = w + same*0.1 * (1.0 - w) return wn

  Read the article

• Segmentation fault on writing char to char* address

  - by Lukas Dojcak

  hi guys, i've got problem with my little C program. Maybe you could help me. char* shiftujVzorku(char* text, char* pattern, int offset){ char* pom = text; int size = 0; int index = 0; while(*(text + size) != '\0'){ size++; } while(*(pom + index) != '\0'){ if(overVzorku(pom + index, pattern)){ while(*pattern != '\0'){ //vyment *pom s *pom + offset if(pom + index + offset < text + size){ char x = *(pom + index + offset); char y = *(pom + index); int adresa = *(pom + index + offset); *(pom + index + offset) = y; <<<<<< SEGMENTATION FAULT *(pom + index) = x; //*pom = *pom - *(pom + offset); //*(pom + offset) = *(pom + offset) + *pom; //*pom = *(pom + offset) - *pom; } else{ *pom = *pom - *(pom + offset - size); *(pom + offset - size) = *(pom + offset - size) + *pom; *pom = *(pom + offset - size) - *pom; } pattern++; } break; } index++; } return text; } Isn't important what's the programm doing. Mayby there's lot of bugs. But, why do I get SEGMENTATION FAULT (for destination see code) at this line? I'm, trying to write some char value to memory space, with help of address "pom + offset + index". Thanks for everything helpful. :)

  Read the article

• MsSql Server high Resource Waits and Head Blocker

  - by MartinHN

  Hi I have a MS SQL Server 2008 Standard installation running a database for a webshop. The current size of the database is 2.5 GB. Running on Windows 2008 Standard. Dual Intel Xeon X5355 @ 2.00 GHz. 4 GB RAM. When I open the Activity Monitor, I see that I have a Wait Time (ms/sec) of 5000 in the "Other" category. In the Processes list, all connections from the webshop, the Head Blocker value is 1. I see every day that when I try to access the website, it can take 20-30 secs before it even starts to "work". I know that it is not network latency. (I have a 301 redirect from the same server that is executed instantly). When the first request has been served, it seems as if it's not a sleep anymore and every subsequent request is served instantly with the speed of light. The problem was worse two weeks ago, until I changed every query to include WITH (NOLOCK). But I still experience the problem, and the Wait times in the Activity Monitor is about the same. The largest table (Images) has 32764 rows (448576 KB). Some tables exceed 300000 rows, thought they're much smaller in size than the Images table. I have the default clustered index for every primary key column, only. Any ideas?

  Read the article

• Javascript: Whitespace Characters being Removed in Chrome (but not Firefox)

  - by Matrym

  Why would the below eliminate the whitespace around matched keyword text when replacing it with an anchor link? Note, this error only occurs in Chrome, and not firefox. For complete context, the file is located at: http://seox.org/lbp/lb-core.js To view the code in action (no errors found yet), the demo page is at http://seox.org/test.html. Copy/Pasting the first paragraph into a rich text editor (ie: dreamweaver, or gmail with rich text editor turned on) will reveal the problem, with words bunched together. Pasting it into a plain text editor will not. // Find page text (not in links) -> doxdesk.com function findPlainTextExceptInLinks(element, substring, callback) { for (var childi= element.childNodes.length; childi-->0;) { var child= element.childNodes[childi]; if (child.nodeType===1) { if (child.tagName.toLowerCase()!=='a') findPlainTextExceptInLinks(child, substring, callback); } else if (child.nodeType===3) { var index= child.data.length; while (true) { index= child.data.lastIndexOf(substring, index); if (index===-1 || limit.indexOf(substring.toLowerCase()) !== -1) break; // don't match an alphanumeric char var dontMatch =/\w/; if(child.nodeValue.charAt(index - 1).match(dontMatch) || child.nodeValue.charAt(index+keyword.length).match(dontMatch)) break; // alert(child.nodeValue.charAt(index+keyword.length + 1)); callback.call(window, child, index) } } } } // Linkup function, call with various type cases (below) function linkup(node, index) { node.splitText(index+keyword.length); var a= document.createElement('a'); a.href= linkUrl; a.appendChild(node.splitText(index)); node.parentNode.insertBefore(a, node.nextSibling); limit.push(keyword.toLowerCase()); // Add the keyword to memory urlMemory.push(linkUrl); // Add the url to memory } // lower case (already applied) findPlainTextExceptInLinks(lbp.vrs.holder, keyword, linkup); Thanks in advance for your help. I'm nearly ready to launch the script, and will gladly comment in kudos to you for your assistance.

  Read the article

• SQLAlchemy: select over multiple tables

  - by ahojnnes

  Hi, I wanted to optimize my database query: link_list = select( columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in], whereclause=and_( not_(link_table.c.id.in_( select( columns=[request_table.c.recipient], whereclause=request_table.c.donator==donator.id ).as_scalar() )), link_table.c.id!=donator.id, ), limit=20, ).execute().fetchall() and tried to merge those two selects in one query: link_list = select( columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in], whereclause=and_( link_table.c.active==True, link_table.c.id!=donator.id, request_table.c.donator==donator.id, link_table.c.id!=request_table.c.recipient, ), limit=20, order_by=[link_table.c.rating.desc()] ).execute().fetchall() the database-schema looks like: link_table = Table('links', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('url', Unicode(250), index=True, unique=True), Column('registration_date', DateTime), Column('donations_in', Integer), Column('active', Boolean), ) request_table = Table('requests', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('recipient', Integer, ForeignKey('links.id')), Column('donator', Integer, ForeignKey('links.id')), Column('date', DateTime), ) There are several links (donator) in request_table pointing to one link in the link_table. I want to have links from link_table, which are not yet "requested". But this does not work. Is it actually possible, what I'm trying to do? If so, how would you do that? Thank you very much in advance!

  Read the article

• PostgreSQL, Foreign Keys, Insert speed & Django

  - by Miles

  A few days ago, I ran into an unexpected performance problem with a pretty standard Django setup. For an upcoming feature, we have to regenerate a table hourly, containing about 100k rows of data, 9M on the disk, 10M indexes according to pgAdmin. The problem is that inserting them by whatever method literally takes ages, up to 3 minutes of 100% disk busy time. That's not something you want on a production site. It doesn't matter if the inserts were in a transaction, issued via plain insert, multi-row insert, COPY FROM or even INSERT INTO t1 SELECT * FROM t2. After noticing this isn't Django's fault, I followed a trial and error route, and hey, the problem disappeared after dropping all foreign keys! Instead of 3 minutes, the INSERT INTO SELECT FROM took less than a second to execute, which isn't too surprising for a table <= 20M on the disk. What is weird is that PostgreSQL manages to slow down inserts by 180x just by using 3 foreign keys. Oh, disk activity was pure writing, as everything is cached in RAM; only writes go to the disks. It looks like PostgreSQL is working very hard to touch every row in the referred tables, as 3MB/sec * 180s is way more data than the 20MB this new table takes on disk. No WAL for the 180s case, I was testing in psql directly, in Django, add ~50% overhead for WAL logging. Tried @commit_on_success, same slowness, I had even implemented multi row insert and COPY FROM with psycopg2. That's another weird thing, how can 10M worth of inserts generate 10x 16M log segments? Table layout: id serial primary, a bunch of int32, 3 foreign keys to small table, 198 rows, 16k on disk large table, 1.2M rows, 59 data + 89 index MB on disk large table, 2.2M rows, 198 + 210MB So, am I doomed to either drop the foreign keys manually or use the table in a very un-Django way by defining saving bla_id x3 and skip using models.ForeignKey? I'd love to hear about some magical antidote / pg setting to fix this.

  Read the article

• How to export Oracle statistics

  - by A_M

  Hi, I am writing some new SQL queries and want to check the query plans that the Oracle query optimiser would come up with in production. My development database doesn't have anything like the data volumes of the production database. How can I export database statistics from a production database and re-import them into a development database? I don't have access to the production database, so I can't simply generate explain plans on production without going through a third party hosting organisation. This is painful. So I want a local database which is in some way representative of production on which I can try out different things. Also, this is for a legacy application. I'd like to "improve" the schema, by adding appropriate indexes. constraints, etc. I need to do this in my development database first, before rolling out to test and production. If I add an index and re-generate statistics in development, then the statistics will be generated around the development data volumes, which makes it difficult to assess the impact my changes on production. Does anyone have any tips on how to deal with this? Or is it just a case of fixing unexpected behaviour once we've discovered it on production? I do have a staging database with production volumes, but again I have to go through a third party to run queries against this, which is painful. So I'm looking for ways to cut out the middle man as much as possible. All this is using Oracle 9i. Thanks.

  Read the article

• Good strategy for copying a "sliding window" of data from a table?

  - by chiborg

  I have a MySQL table from a third-party application that has millions of rows and only one index - the timestamp of each entry. Now I want to do some heavy self-joins and queries on the data using fields other than the timestamp. Doing the query on the original table would bring the database to a crawl, adding indexes to the table is not an option. Additionally, I only need entries that are newer than one week. My current strategy for doing the queries efficiently is to use a separate table (aux_table) that has the necessary indexes. My questions are: Is there another way to do the queries? and if not, How do I update the data in the indexed table efficiently? So far I have found two approaches for updating aux_table: Truncate aux_table and insert the desired data from the original table. Not very efficient because all the indexes must be re-crated. Check for the biggest timestamp in aux_table and insert all entries with a greater or equal timestamp from the original table. Occasionally drop older entries. Only copying entries with greater timestamp leads to dropped entries (because of entries with same timestamp that were inserted into the original table after the last update).

  Read the article

• Deeply nested subqueries for traversing trees in MySQL

  - by nickf

  I have a table in my database where I store a tree structure using the hybrid Nested Set (MPTT) model (the one which has lft and rght values) and the Adjacency List model (storing parent_id on each node). my_table (id, parent_id, lft, rght, alias) This question doesn't relate to any of the MPTT aspects of the tree but I thought I'd leave it in in case anyone had a good idea about how to leverage that. I want to convert a path of aliases to a specific node. For example: "users.admins.nickf" would find the node with alias "nickf" which is a child of one with alias "admins" which is a child of "users" which is at the root. There is a unique index on (parent_id, alias). I started out by writing the function so it would split the path to its parts, then query the database one by one: SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users';-- 1 SELECT `id` FROM `my_table` WHERE `parent_id` = 1 AND `alias` = 'admins'; -- 8 SELECT `id` FROM `my_table` WHERE `parent_id` = 8 AND `alias` = 'nickf'; -- 37 But then I realised I could do it with a single query, using a variable amount of nesting: SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users' ) AND `alias` = 'admins' ) AND `alias` = 'nickf'; Since the number of sub-queries is dependent on the number of steps in the path, am I going to run into issues with having too many subqueries? (If there even is such a thing) Are there any better/smarter ways to perform this query?

  Read the article

• How can I optimize the SELECT statement running on an Oracle database?

  - by Elvis Lou

  I have a SELECT statement in ORACLE: SELECT COUNT(DISTINCT ds1.endpoint_msisdn) multiple30, dss1.service, dss1.endpoint_provisioning_id, dss1.company_scope, Nvl(x.subscription_status, dss1.subscription_status) subscription_status FROM daily_summary ds1 join daily_summary ds2 ON ds1.endpoint_msisdn = ds2.endpoint_msisdn, daily_summary_static dss1, daily_summary_static dss2, (SELECT NULL subscription_status FROM dual UNION ALL SELECT -2 subscription_status FROM dual) x WHERE ds1.summary_ts >= To_date('10-04-2012', 'dd-mm-yyyy') - 30 AND ds1.summary_ts <= To_date('10-04-2012', 'dd-mm-yyyy') AND dss1.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30 AND dss1.last_active <= To_date('10-04-2012', 'dd-mm-yyyy') AND dss2.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30 AND dss2.last_active <= To_date('10-04-2012', 'dd-mm-yyyy') AND dss1.service <> dss2.service AND ( dss1.company_scope = 2 OR dss1.company_scope = 5 ) AND ( dss2.company_scope = 2 OR dss2.company_scope = 5 ) AND dss1.company_scope = dss2.company_scope AND ds1.endpoint_noc_id = dss1.endpoint_noc_id AND ds1.endpoint_host_id = dss1.endpoint_host_id AND ds1.endpoint_instance_id = dss1.endpoint_instance_id AND ds2.endpoint_noc_id = dss2.endpoint_noc_id AND ds2.endpoint_host_id = dss2.endpoint_host_id AND ds2.endpoint_instance_id = dss2.endpoint_instance_id AND dss1.endpoint_provisioning_id = dss2.endpoint_provisioning_id AND Least(1, ds1.total_actions) = 1 AND Least(1, ds2.total_actions) = 1 GROUP BY dss1.service, dss1.endpoint_provisioning_id, dss1.company_scope, Nvl(x.subscription_status, dss1.subscription_status); This query took about 26 minutes to return in my environment, but if I remove the section: dss1.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND dss1.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND dss2.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND dss2.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND it only took 20 seconds to run. We have index on the column last_active, I don't know why the section slow down the performance so much? any ideas?

  Read the article

• PyQt QAbstractListModel seems to ignore tristate flags

  - by mcieslak

  I've been trying for a couple days to figure out why my QAbstractLisModel won't allow a user to toggle a checkable item in three states. The model returns the Qt.IsTristate and Qt.ItemIsUserCheckable in the flags() method, but when the program runs only Qt.Checked and Qt.Unchecked are toggled on edit. class cboxModel(QtCore.QAbstractListModel): def __init__(self, parent=None): super(cboxModel, self).__init__(parent) self.cboxes = [ ['a',0], ['b',1], ['c',2], ['d',0] ] def rowCount(self,index=QtCore.QModelIndex()): return len(self.cboxes) def data(self,index,role): if not index.isValid: return QtCore.QVariant() myname,mystate = self.cboxes[index.row()] if role == QtCore.Qt.DisplayRole: return QtCore.QVariant(myname) if role == QtCore.Qt.CheckStateRole: if mystate == 0: return QtCore.QVariant(QtCore.Qt.Unchecked) elif mystate == 1: return QtCore.QVariant(QtCore.Qt.PartiallyChecked) elif mystate == 2: return QtCore.QVariant(QtCore.Qt.Checked) return QtCore.QVariant() def setData(self,index,value,role=QtCore.Qt.EditRole): if index.isValid(): self.cboxes[index.row()][1] = value.toInt()[0] self.emit(QtCore.SIGNAL("dataChanged(QModelIndex,QModelIndex)"), index, index) print self.cboxes return True return False def flags(self,index): if not index.isValid(): return QtCore.Qt.ItemIsEditable return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsEditable | QtCore.Qt.ItemIsUserCheckable | QtCore.Qt.ItemIsTristate You can test it with this, class MainForm(QtGui.QMainWindow): def __init__(self, parent=None): super(MainForm, self).__init__(parent) model = cboxModel(self) self.view = QtGui.QListView() self.view.setModel(model) self.setCentralWidget(self.view) app = QtGui.QApplication(sys.argv) form = MainForm() form.show() app.exec_() and see that only 2 states are available. I'm assuming there's something simple I'm missing. Any ideas? Thanks!

  Read the article

• Problems with string parameter insertion into prepared statement

  - by c0d3x

  Hi, I have a database running on an MS SQL Server. My application communicates via JDBC and ODBC with it. Now I try to use prepared statements. When I insert a numeric (Long) parameter everything works fine. When I insert a string parameter it does not work. There is no error message, but an empty result set. WHERE column LIKE ('%' + ? + '%') --inserted "test" -> empty result set WHERE column LIKE ? --inserted "%test%" -> empty result set WHERE column = ? --inserted "test" -> works But I need the LIKE functionality. When I insert the same string directly into the query string (not as a prepared statement parameter) it runs fine. WHERE column LIKE '%test%' It looks a little bit like double quoting for me, but I never used quotes inside a string. I use preparedStatement.setString(int index, String x) for insertion. What is causing this problem? How can I fix it? Thanks in advance.

  Read the article

• Javascript indexOf() always returns -1

  - by Thomas

  I try to retrieve the index of en element in an array. This works perfectly var onCreate = function (event) { console.assert(markers[markerId] === undefined); var markerId = event.markerId; markers[markerId] = {}; var marker = markers[markerId]; // create the container object marker.object3d = new THREE.Object3D(); marker.object3d.matrixAutoUpdate = false; scene.add(marker.object3d); //UPDATE ARRAY HOLDING CURRENT MARKERS console.log("ON CREATE"); console.log("current detected markers: " + currentMarkers); var idx = currentMarkers.indexOf(markerId); // Find the index console.log("marker " + event.markerId + " has index: " + idx); if(idx==-1) // if does not exist currentMarkers.push(markerId); But this doesnt... var onDelete = function (event) { console.assert(markers[event.markerId] !== undefined); var markerId = event.markerId; //UPDATE ARRAY HOLDING CURRENT MARKERS console.log("ON DELETE"); console.log("current detected markers: " + currentMarkers); var idxx = currentMarkers.indexOf(markerId); // Find the index console.log("marker " + markerId + " has index: " + idxx); if(idxx != -1) {// if DOES exist currentMarkers.splice(idxx, 1); //Delete var idxxx = currentMarkersCoverChecked.indexOf(markerId); // Find the index if(idxxx == -1) // if does NOT exist, so if not being checked checkMarkerCovered(markerId); } onDeleteRandom(markerId); var marker = markers[markerId]; scene.remove(marker.object3d); delete markers[markerId]; Look at my console output: ON CREATE current detected markers: 2,1,4 marker 3 has index: -1 ON CREATE main.js:266 current detected markers: 2,1,4,3 marker 4 has index: 2 ON DELETE current detected markers: 2,1,4,3 marker 2 has index: -1 ON CREATE current detected markers: 2,1,4,3 marker 2 has index: 0 ON DELETE current detected markers: 2,1,4,3 marker 1 has index: -1

  Read the article

• Simple aggregating query very slow in PostgreSql, any way to improve?

  - by Ash

  HI I have a table which holds files and their types such as CREATE TABLE files ( id SERIAL PRIMARY KEY, name VARCHAR(255), filetype VARCHAR(255), ... ); and another table for holding file properties such as CREATE TABLE properties ( id SERIAL PRIMARY KEY, file_id INTEGER CONSTRAINT fk_files REFERENCES files(id), size INTEGER, ... // other property fields ); The file_id field has an index. The file table has around 800k lines, and the properties table around 200k (not all files necessarily have/need a properties). I want to do aggregating queries, for example find the average size and standard deviation for all file types. But it's very slow - around 70 seconds for the latter query. I understand it needs a sequential scan, but still it seems too much. Here's the query SELECT f.filetype, avg(size), stddev(size) FROM files as f, properties as pr WHERE f.id = pr.file_id GROUP BY f.filetype; and the explain HashAggregate (cost=140292.20..140293.94 rows=116 width=13) (actual time=74013.621..74013.954 rows=110 loops=1) -> Hash Join (cost=6780.19..138945.47 rows=179564 width=13) (actual time=1520.104..73156.531 rows=179499 loops=1) Hash Cond: (f.id = pr.file_id) -> Seq Scan on files f (cost=0.00..108365.41 rows=1140941 width=9) (actual time=0.998..62569.628 rows=805270 loops=1) -> Hash (cost=3658.64..3658.64 rows=179564 width=12) (actual time=1131.053..1131.053 rows=179499 loops=1) -> Seq Scan on properties pr (cost=0.00..3658.64 rows=179564 width=12) (actual time=0.753..557.171 rows=179574 loops=1) Total runtime: 74014.520 ms Any ideas why it is so slow/how to make it faster?

  Read the article

• integrating jquery with AJAX using MVC for ddl/html.dropdownlist

  - by needhelp

  the situation: a user on the page in question selects a category from a dropdown which then dynamically populates all the users of that category in a second dropdown beside it. all the data is being retrieved using LinqtoSQL and i was wondering if this can be done a) using html.dropdownlist in a strongly typed view? b) using jquery to trigger the ajax request on selected index change instead of a 'populate' button trigger? sorry i dont have code as what i was trying really wasnt working at all. I am having trouble with how to do it conceptually and programatically! will appreciate any links to examples etc greatly! thanks in advance! EDIT: this is kind of what i was trying to achieve.. first the ViewPage: <script type="text/javascript"> $(document).ready function TypeSearch() { $.getJSON("/Home/Type", null, function(data) { //dont know what to do here }); } </script> <p> <label for="userType">userType:</label> <%= Html.DropDownList("userType") %> <%= Html.ValidationMessage("userType", "*") %> <input type="submit" runat="server" onclick="TypeSearch()" /> <label for="accountNumber">accountNumber:</label> <%= Html.DropDownList("accountNumber") %> <%= Html.ValidationMessage("accountNumber", "*") %> </p> Then home controller action: public ActionResult Type() { string accountType = dropdownvalue; List<Account> accounts = userRep.GetAccountsByType(accountType).ToList(); return Json(accounts); }

  Read the article

• INSERT INTO ...SELECT syntax error in join operator

  - by user1477356

  I'm trying to write a shopping basket into a order + orderline in a sql database from C# asp.net. the orderline will contain a ordernumber, total price, productid, quantity etc. for every item in the basket. The order itself will contain the ordernumber as primary key and will be linked to the different lines through it. Everything worked fine yesterday, but now as i tried to use a SELECT command in the insert into statement to get things more dynamic i'm getting the above described syntax error. Does anybody know what's wrong with this statement: INSERT INTO [order] (klant_id,totaalprijs,btw,subtotaal,verzendkosten) SELECT klant.id , SUM(orderregel.totaalprijs) , SUM(orderregel.btw) , SUM(orderregel.totaalprijs) - SUM(orderregel.btw) , 7.50 FROM orderregel INNER JOIN klant ON [order].klant_id = klant.id WHERE klant.username = 'jerry' GROUP BY id; the ordernumber in the "order" table is on autonumber, in the asp codebehind there is a for each which handles the lines being written for every product, there's an index set on 0 outside of this loop and is heightened with 1 every end of it. The executenonquery of the order is only executed once at the beginning of the first loop and the lines are added after with MAX(ordernumber) as ordernumber. I hope i have provided enough information and somebody is capable of helping me. Thanks in advance!

  Read the article

• Would you allow this type of query?

  - by user564577

  I'm exploring using an ORM tool in our development shop, and in particular Entity Framework 4.0. Since we work with VERY large databases, I'm a bit concerned about the query's it generates. Doing something simple like getting clients with an address in a state looks like below. As a database developer or admin would you allow this? Is it as bad as it looks? Assume every join is on a clustered index. SELECT [Project2].[ClientKey] AS [ClientKey], [Project2].[FirstName] AS [FirstName], [Project2].[LastName] AS [LastName], [Project2].[IsEnabled] AS [IsEnabled], [Project2].[ChangeUser] AS [ChangeUser], [Project2].[ChangeDate] AS [ChangeDate], [Project2].[C1] AS [C1], [Project2].[AddressKey] AS [AddressKey], [Project2].[ClientKey1] AS [ClientKey1], [Project2].[AddressTypeCode] AS [AddressTypeCode], [Project2].[PrimaryAddress] AS [PrimaryAddress], [Project2].[AddressLine1] AS [AddressLine1], [Project2].[AddressLine2] AS [AddressLine2], [Project2].[City] AS [City], [Project2].[State] AS [State], [Project2].[ZIP] AS [ZIP] FROM ( SELECT [Distinct1].[ClientKey] AS [ClientKey], [Distinct1].[FirstName] AS [FirstName], [Distinct1].[LastName] AS [LastName], [Distinct1].[IsEnabled] AS [IsEnabled], [Distinct1].[ChangeUser] AS [ChangeUser], [Distinct1].[ChangeDate] AS [ChangeDate], [Extent3].[AddressKey] AS [AddressKey], [Extent3].[ClientKey] AS [ClientKey1], [Extent3].[AddressTypeCode] AS [AddressTypeCode], [Extent3].[PrimaryAddress] AS [PrimaryAddress], [Extent3].[AddressLine1] AS [AddressLine1], [Extent3].[AddressLine2] AS [AddressLine2], [Extent3].[City] AS [City], [Extent3].[State] AS [State], [Extent3].[ZIP] AS [ZIP], CASE WHEN ([Extent3].[AddressKey] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT DISTINCT [Extent1].[ClientKey] AS [ClientKey], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[IsEnabled] AS [IsEnabled], [Extent1].[ChangeUser] AS [ChangeUser], [Extent1].[ChangeDate] AS [ChangeDate] FROM [Common].[Clients] AS [Extent1] INNER JOIN [Common].[ClientAddresses] AS [Extent2] ON [Extent1].[ClientKey] = [Extent2].[ClientKey] WHERE (( CAST(CHARINDEX(UPPER('D'), UPPER([Extent1].[LastName])) AS int)) > 0) AND ([Extent1].[IsEnabled] = 1) AND ([Extent2].[City] IS NOT NULL) AND ((UPPER([Extent2].[City])) = (UPPER('Colorado Springs'))) ) AS [Distinct1] LEFT OUTER JOIN [Common].[ClientAddresses] AS [Extent3] ON [Distinct1].[ClientKey] = [Extent3].[ClientKey] ) AS [Project2] ORDER BY [Project2].[ClientKey] ASC, [Project2].[FirstName] ASC, [Project2].[LastName] ASC, [Project2].[IsEnabled] ASC, [Project2].[ChangeUser] ASC, [Project2].[ChangeDate] ASC, [Project2].[C1] ASC

  Read the article

< Previous Page | 600 601 602 603 604 605 606 607 608 609 610 611  | Next Page >