Search Results

Search found 21589 results on 864 pages for 'primary key'.

Page 301/864 | < Previous Page | 297 298 299 300 301 302 303 304 305 306 307 308  | Next Page >

• SQL Server Express performance issue

  - by Developer IT

  Hi folks ! I know my questions will sound silly and probably nobody will have perfect answer but since I am in a complete dead-end with the situation it will make me feel better to post it here. So... I have a SQL Server Express database that's 500 Mb. It contains 5 tables and maybe 30 stored procedure. This database is use to store articles and is use for the Developer It web site. Normally the web pages load quickly, let's say 2 ou 3 sec. BUT, sqlserver process uses 100% of the processor for those 2 or 3 sec. I try to find which stored procedure was the problem and I could not find one. It seems like every read into the table dans contains the articles (there are about 155,000 of them and 20 or so gets added every 15 minutes). I added few index but without luck... It is because the table is full text indexed ? Should I have order with the primary key instead of date ? I never had any problems with ordering by dates.... Should I use dynamic SQL ? Should I add the primary key into the url of the articles ? Should I use mutiple indexes for seperate columns or one big index ? I you want more details or code bits, just ask for it. Basicly, every little hint is much apreciated. Thanks.

  Read the article

• Simple Database normalization question...

  - by user365531

  Hi all, I have a quick question regarding a database that I am designing and making sure it is normalized... I have a customer table, with a primary key of customerId. It has a StatusCode column that has a code which reflects the customers account status ie. 1 = Open, 2 = Closed, 3 = Suspended etc... Now I would like to have another field in the customer table that flags whether the account is allowed to be suspended or not... certain customers will be automatically suspended if they break there trading terms... others not... so the relevant table fields will be as so: Customers (CustomerId(PK):StatusCode:IsSuspensionAllowed) Now both fields are dependent on the primary key as you can not determine the status or whether suspensions are allowed on a particular customer unless you know the specific customer, except of course when the IsSuspensionAllowed field is set to YES, the the customer should never have a StatusCode of 3 (Suspended). It seems from the above table design it is possible for this to happen unless a check contraint is added to my table. I can't see how another table could be added to the relational design to enforce this though as it's only in the case where IsSuspensionAllowed is set to YES and StatusCode is set to 3 when the two have a dependence on each other. So after my long winded explanation my question is this: Is this a normalization problem and I'm not seeing a relational design that will enforce this... or is it actually just a business rule that should be enforced with a check contraint and the table is in fact still normalized. Cheers, Steve

  Read the article

• Distribute budget over for ranked components in SQL

  - by Lee

  Assume I have a budget of $10 (any integer) and I want to distribute it over records which have rank field with varying needs. Example: rank Req. Fulfilled? 1 $3 Y 2 $4 Y 3 $2 Y 4 $3 N Those ranks from 1 to 3 should be fulfilled because they are within budget. whereas, the one ranked 4 should not. I want an SQL query to solve that. Below is my initial script: CREATE TABLE budget ( id VARCHAR (32), budget INTEGER, PRIMARY KEY (id)); CREATE TABLE component ( id VARCHAR (32), rank INTEGER, req INTEGER, satisfied BOOLEAN, PRIMARY KEY (id)); INSERT INTO budget (id,budget) VALUES ('1',10); INSERT INTO component (id,rank,req) VALUES ('1',1,3); INSERT INTO component (id,rank,req) VALUES ('2',2,4); INSERT INTO component (id,rank,req) VALUES ('3',3,2); INSERT INTO component (id,rank,req) VALUES ('4',4,3); Thanks in advance for your help. Lee

  Read the article

• Insert data in an object to a database.

  - by paul

  I am facing the following design/implementation dilemma. I have a class Customer which is below with getters and setters. I would like to insert the value of the Customer into a "Customer" table of a database. But Customer has an address which is of type "Address". How do I go about inserting this field into the database?(I am using sqlite3). I thought of writing a separate table "Address(customerId,doorNo,city,state,country,pinCode)". But I am having second thoughts about generating the primary key(customerId) which should be same for both the "customer" and "Address" table. Sqlite3 faq states that I can do "Integer Primary Key" to use the field to generate an auto number. But if I do that in customer table, I would have to retrieve the same Id to be used in Address table. This kinda looks wrong to me :-?. There should be an elegant method to solve this. Any ideas would be much appreciated. Thanks in advance. import java.io.*; import java.sql.*; class Customer { private String id; private String name; private Address address; private Connection connection; private ResultSet resultSet; private PreparedStatement preparedStatement; public void insertToDatabase(){ } } class Address{ private String doorNumber; private String streetName; private String cityName; private String districtName; private String stateName; private String countryName; private long pinCode; }

  Read the article

• Getting the most recent post based on date

  - by camcim

  Hi guys, How do I go about displaying the most recent post when I have two tables, both containing a column called creation_date This would be simple if all I had to do was get the most recent post based on posts created_on value however if a post contains replies I need to factor this into the equation. If a post has a more recent reply I want to get the replies created_on value but also get the posts post_id and subject. The posts table structure: CREATE TABLE `posts` ( `post_id` bigint(20) unsigned NOT NULL auto_increment, `cat_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `subject` tinytext NOT NULL, `comments` text NOT NULL, `created_on` datetime NOT NULL, `status` varchar(10) NOT NULL default 'INACTIVE', `private_post` varchar(10) NOT NULL default 'PUBLIC', `db_location` varchar(10) NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; The replies table structure: CREATE TABLE `replies` ( `reply_id` bigint(20) unsigned NOT NULL auto_increment, `post_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `comments` text NOT NULL, `created_on` datetime NOT NULL, `notify` varchar(5) NOT NULL default 'YES', `status` varchar(10) NOT NULL default 'INACTIVE', `db_location` varchar(10) NOT NULL, PRIMARY KEY (`reply_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; Here is my query so far. I've removed my attempt of extracting the dates. $strQuery = "SELECT posts.post_id, posts.created_on, replies.created_on, posts.subject "; $strQuery = $strQuery."FROM posts ,replies "; $strQuery = $strQuery."WHERE posts.post_id = replies.post_id "; $strQuery = $strQuery."AND posts.cat_id = '".$row->cat_id."'";

  Read the article

• copy rows before updating them to preserve archive in Postgres

  - by punkish

  I am experimenting with creating a table that keeps a version of every row. The idea is to be able to query for how the rows were at any point in time even if the query has JOINs. Consider a system where the primary resource is books, that is, books are queried for, and author info comes along for the ride CREATE TABLE authors ( author_id INTEGER NOT NULL, version INTEGER NOT NULL CHECK (version > 0), author_name TEXT, is_active BOOLEAN DEFAULT '1', modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (author_id, version) ) INSERT INTO authors (author_id, version, author_name) VALUES (1, 1, 'John'), (2, 1, 'Jack'), (3, 1, 'Ernest'); I would like to be able to update the above like so UPDATE authors SET author_name = 'Jack K' WHERE author_id = 1; and end up with 2, 1, Jack, t, 2012-03-29 21:35:00 2, 2, Jack K, t, 2012-03-29 21:37:40 which I can then query with SELECT author_name, modified_on FROM authors WHERE author_id = 2 AND modified_on < '2012-03-29 21:37:00' ORDER BY version DESC LIMIT 1; to get 2, 1, Jack, t, 2012-03-29 21:35:00 Something like the following doesn't really work CREATE OR REPLACE FUNCTION archive_authors() RETURNS TRIGGER AS $archive_author$ BEGIN IF (TG_OP = 'UPDATE') THEN -- The following fails because author_id,version PK already exists INSERT INTO authors (author_id, version, author_name) VALUES (OLD.author_id, OLD.version, OLD.author_name); UPDATE authors SET version = OLD.version + 1 WHERE author_id = OLD.author_id AND version = OLD.version; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $archive_author$ LANGUAGE plpgsql; CREATE TRIGGER archive_author AFTER UPDATE OR DELETE ON authors FOR EACH ROW EXECUTE PROCEDURE archive_authors(); How can I achieve the above? Or, is there a better way to accomplish this? Ideally, I would prefer to not create a shadow table to store the archived rows.

  Read the article

• gridview image column problem

  - by jame

  Work on vs05 C# asp.net .My SQL Syntax is :**** if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Images] GO CREATE TABLE [dbo].[Images] ( [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [ImageName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Image] [image] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO I want to show this Images table values in a grid view.....I do it ...but the image value can not show ....asp.net syntax for gridview is <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="ImageName" HeaderText="ImageName" /> <asp:TemplateField HeaderText="Image"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("Image") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("Image") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> i write the below code on pageload event. i want images table values must shown when the page is load... string strSQL = "Select * From Images"; DataTable dt = clsDB.getDataTable(strSQL); this.GridView2.DataSource = dt; this.GridView2.DataBind(); Why not i get the image on my image column of the gridview.....what's the problem is how to solve?

  Read the article

• Optimizing an embedded SELECT query in mySQL

  - by Crazy Serb

  Ok, here's a query that I am running right now on a table that has 45,000 records and is 65MB in size... and is just about to get bigger and bigger (so I gotta think of the future performance as well here): SELECT count(payment_id) as signup_count, sum(amount) as signup_amount FROM payments p WHERE tm_completed BETWEEN '2009-05-01' AND '2009-05-30' AND completed > 0 AND tm_completed IS NOT NULL AND member_id NOT IN (SELECT p2.member_id FROM payments p2 WHERE p2.completed=1 AND p2.tm_completed < '2009-05-01' AND p2.tm_completed IS NOT NULL GROUP BY p2.member_id) And as you might or might not imagine - it chokes the mysql server to a standstill... What it does is - it simply pulls the number of new users who signed up, have at least one "completed" payment, tm_completed is not empty (as it is only populated for completed payments), and (the embedded Select) that member has never had a "completed" payment before - meaning he's a new member (just because the system does rebills and whatnot, and this is the only way to sort of differentiate between an existing member who just got rebilled and a new member who got billed for the first time). Now, is there any possible way to optimize this query to use less resources or something, and to stop taking my mysql resources down on their knees...? Am I missing any info to clarify this any further? Let me know... EDIT: Here are the indexes already on that table: PRIMARY PRIMARY 46757 payment_id member_id INDEX 23378 member_id payer_id INDEX 11689 payer_id coupon_id INDEX 1 coupon_id tm_added INDEX 46757 tm_added, product_id tm_completed INDEX 46757 tm_completed, product_id

  Read the article

• Improving performance in this query

  - by Luiz Gustavo F. Gama

  I have 3 tables with user logins: sis_login = administrators tb_rb_estrutura = coordinators tb_usuario = clients I created a VIEW to unite all these users by separating them by levels, as follows: create view `login_names` as select `n1`.`cod_login` as `id`, '1' as `level`, `n1`.`nom_user` as `name` from `dados`.`sis_login` `n1` union all select `n2`.`id` as `id`, '2' as `level`, `n2`.`nom_funcionario` as `name` from `tb_rb_estrutura` `n2` union all select `n3`.`cod_usuario` as `id`, '3' as `level`, `n3`.`dsc_nome` as `name` from `tb_usuario` `n3`; So, can occur up to three ids repeated for different users, which is why I separated by levels. This VIEW is just to return me user name, according to his id and level. considering it has about 500,000 registered users, this view takes about 1 second to load. too much time, but is becomes very small when I need to return the latest posts on the forum of my website. The tables of the forums return the user id and level, then look for a name in this VIEW. I have registered 18 forums. When I run the query, it takes one second for each forum = 18 seconds. OMG. This page loads every time somebody enter my website. This is my query: select `x`.`forum_id`, `x`.`topic_id`, `l`.`nome` from ( select `t`.`forum_id`, `t`.`topic_id`, `t`.`data`, `t`.`user_id`, `t`.`user_level` from `tb_forum_topics` `t` union all select `a`.`forum_id`, `a`.`topic_id`, `a`.`data`, `a`.`user_id`, `a`.`user_level` from `tb_forum_answers` `a` ) `x` left outer join `login_names` `l` on `l`.`id` = `x`.`user_id` and `l`.`level` = `x`.`user_level` group by `x`.`forum_id` asc USING EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 530415 4 DERIVED n1 ALL NULL NULL NULL NULL 114 5 UNION n2 ALL NULL NULL NULL NULL 2 6 UNION n3 ALL NULL NULL NULL NULL 530299 NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2 DERIVED t ALL NULL NULL NULL NULL 3 3 UNION r ALL NULL NULL NULL NULL 3 NULL UNION RESULT ALL NULL NULL NULL NULL NULL Somebody can help me or give a suggestion?

  Read the article

• Why doesn't my test run tearDownAfterTestClass when it fails

  - by Memor-X

  in a test i am writing the setUpBeforeTests creates a new customer in the database who is then used to perform the tests with so naturally when i finish the test i should get rid of this test customer in tearDownAfterTestClass so that i can create then anew when i rerun the test and not get any false positives how when the tests all run fine i have no problem but if a test fails and i go to rerun it my setUpBeforeTests fails because i check for mysql errors in it like this try { if(!mysqli_query($connection,$query)) { $this->assertTrue(false); } } catch (Exception $exc) { $msg = '[tearDownAfterTestClass] Exception Error' . PHP_EOL . PHP_EOL; $msg .= 'Could not run query - '.mysqli_error($connection). PHP_EOL;; $this->fail($msg); } the error i get is that there is a primary key violation which is expected cause i'm trying to create a new customer using the same data (primary key is on email which is also used to log in) however that means when the test failed it didn't run tearDownAfterTestClass now i could just move everything in tearDownAfterTestClass to the start of setUpBeforeTests however to me that seems like bad programming since it defeates the purpose of even have tearDownAfterTestClass so i am wondering, why isn't my tearDownAfterTestClass running when a test fails NOTE: the database is a fundamental part of the system i'm testing and the database and system are on a separate development environment not the live one, the backup files for the database are almost 2 GBs and takes almost 1/2 an hour to restore, the purpose of the tear down is to remove any data we have added because of the test so that we don't have to restore the database every time we run the tests

  Read the article

• table subtraction challenge

  - by Valentin

  I have a challenge that I haven’t overcome in the last two days using Stored Procedures and SQL 2008. I took several approaches but must fell short. One appraoch very interesting was using a table substraction. It’s really all about table subtraction. I was wondering if you could help me crack this one. Here is the challenge: Two tables 1Testdb y 2Testdb. My first step was to select ID relationships ([2Testdb].Acc_id) on table 2Testdb for one given individual ([2Testdb].Bus_id). Then query table 1Testdb for records not mathcing my original selection from 2Testdb. But other approaches are welcome. Data and Structures: USE [Challengedb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[1Testdb]( [Acc_id] [uniqueidentifier] NULL [Name] [Varchar(10)] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[2Testdb]( [Acc_id] [uniqueidentifier] NULL, [Bus_id] [uniqueidentifier] NULL ) ON [PRIMARY] GO Records on 1Testdb: 34455F60-9474-4521-804E-66DB39A579F3, John C23523F6-2309-4F58-BB3F-EF7486C7AF8B, Pete DC711615-3BE4-4B31-9EF2-B1314185CA62, Dave E3AAB073-2398-476D-828B-92829F686A4C, Adam Records on 2Testdb: (Relationship table, ex. Friend relationships) Record #1: DC711615-3BE4-4B31-9EF2-B1314185CA62, 34455F60-9474-4521-804E-66DB39A579F3 Record #2: E3AAB073-2398-476D-828B-92829F686A4C, 34455F60-9474-4521-804E-66DB39A579F3 Record # 3: DC711615-3BE4-4B31-9EF2-B1314185CA62, E3AAB073-2398-476D-828B-92829F686A4C Record # 4: E3AAB073-2398-476D-828B-92829F686A4C, DC711615-3BE4-4B31-9EF2-B1314185CA62 Challenge: Select from table 1Testdb only those records distinct that may not have a relationship with John [34455F60-9474-4521-804E-66DB39A579F3] on table 2Testdb. Expected result should be (Who does John doesn’t have relationship with?): C23523F6-2309-4F58-BB3F-EF7486C7AF8B, Pete Thank you, Valentin

  Read the article

• Mysql Performance Question - Essentially about normalizing efficiency

  - by freqmode

  Hi there. Just a quick question about database performance. I'll outline my site purpose below as background. I'm creating a dictionary site that saves the words users define to a database. What I'm wondering is whether or not to create a words table for each user or to keep one massive words table. This site will be used for entire schools so the single words table would be massive! The database structure is as follows: A user table with: User_ID PRIMARY KEY Username First Last Password Email Country Research Standings SendInfo Donated JoinedOn LastLogin Logins Correct Attempts Admin Active And one word table with: User_ID PRIMARY KEY Word Vocab Spell Defined DefinedAttempted Spelled SpelledAttempted Sentenced SentencedAttempted So what I'm asking is , performance-wise, should I create a new table for each user when they join the site - each user could have hundreds or thousands of words over time? Or is it better to have one massive table with thousands and thousands of records and filter by User_ID. I don't think I'll perform many table joins. My gut feeling is to create a new table for each user, but I thought I'd ask for expert advice! Thanks in advance.

  Read the article

• How to figure out which record has been deleted in an effiecient way?

  - by janetsmith

  Hi, I am working on an in-house ETL solution, from db1 (Oracle) to db2 (Sybase). We needs to transfer data incrementally (Change Data Capture?) into db2. I have only read access to tables, so I can't create any table or trigger in Oracle db1. The challenge I am facing is, how to detect record deletion in Oracle? The solution which I can think of, is by using additional standalone/embedded db (e.g. derby, h2 etc). This db contains 2 tables, namely old_data, new_data. old_data contains primary key field from tahle of interest in Oracle. Every time ETL process runs, new_data table will be populated with primary key field from Oracle table. After that, I will run the following sql command to get the deleted rows: SELECT old_data.id FROM old_data WHERE old_data.id NOT IN (SELECT new_data.id FROM new_data) I think this will be a very expensive operation when the volume of data become very large. Do you have any better idea of doing this? Thanks.

  Read the article

• mysql subquery strangely slow

  - by aviv

  I have a query to select from another sub-query select. While the two queries look almost the same the second query (in this sample) runs much slower: SELECT user.id ,user.first_name -- user.* FROM user WHERE user.id IN (SELECT ref_id FROM education WHERE ref_type='user' AND education.institute_id='58' AND education.institute_type='1' ); This query takes 1.2s Explain on this query results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY user index first_name 152 141192 Using where; Using index 2 DEPENDENT SUBQUERY education index_subquery ref_type,ref_id,institute_id,institute_type,ref_type_2 ref_id 4 func 1 Using where The second query: SELECT -- user.id -- user.first_name user.* FROM user WHERE user.id IN (SELECT ref_id FROM education WHERE ref_type='user' AND education.institute_id='58' AND education.institute_type='1' ); Takes 45sec to run, with explain: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY user ALL 141192 Using where 2 DEPENDENT SUBQUERY education index_subquery ref_type,ref_id,institute_id,institute_type,ref_type_2 ref_id 4 func 1 Using where Why is it slower if i query only by index fields? Why both queries scans the full length of the user table? Any ideas how to improve? Thanks.

  Read the article

• How can I improve the performance of LinqToSql queries that use EntitySet properties?

  - by DanM

  I'm using LinqToSql to query a small, simple SQL Server CE database. I've noticed that any operations involving sub-properties are disappointingly slow. For example, if I have a Customer table that is referenced by an Order table, LinqToSql will automatically create an EntitySet<Order> property. This is a nice convenience, allowing me to do things like Customer.Order.Where(o => o.ProductName = "Stopwatch"), but for some reason, SQL Server CE hangs up pretty bad when I try to do stuff like this. One of my queries, which isn't really that complicated takes 3-4 seconds to complete. I can get the speed up to acceptable, even fast, if I just grab the two tables individually and convert them to List<Customer> and List<Order>, then join then manually with my own query, but this is throwing out a lot of what makes LinqToSql so appealing. So, I'm wondering if I can somehow get the whole database into RAM and just query that way, then occasionally save it. Is this possible? How? If not, is there anything else I can do to boost the performance besides resorting to doing all the joins manually? Note: My database in its initial state is about 250K and I don't expect it to grow to more than 1-2Mb. So, loading the data into RAM certainly wouldn't be a problem from a memory point of view. Update Here are the table definitions for the example I used in my question: create table Order ( Id int identity(1, 1) primary key, ProductName ntext null ) create table Customer ( Id int identity(1, 1) primary key, OrderId int null references Order (Id) )

  Read the article

• How to bring a modal to the front of the page using bootstrap?

  - by sharataka

  I am trying to implement a modal using bootstrap. When I click on the button the screen fades and the modal appears, but it appears behind the images in and is unreadable for the user. How do I bring the modal forward? <div class ="container-fluid"> <div class = "row-fluid"> <div class = "span2"> <!-- Modal --> <div class="modal hide fade" id="myModal"> <div class="modal-header"> <a class="close" data-dismiss="modal">×</a> <h3>Modal header</h3> </div> <div class="modal-body"> <p>One fine body…</p> </div> <div class="modal-footer"> <a href="#" class="btn">Close</a> <a href="#" class="btn btn-primary">Save changes</a> </div> </div> <a data-toggle="modal" href="#myModal" class="btn btn-primary">Launch demo modal</a> </div> <div class = "span8"> #a lot of images in this div </div> </div> </div>

  Read the article

• Still Confused About Identifying vs. Non-Identifying Relationships

  - by Jason

  So, I've been reading up on identifying vs. non-identifying relationships in my database design, and a number of the answers on SO seem contradicting to me. Here are the two questions I am looking at: What's the Difference Between Identifying and Non-Identifying Relationships Trouble Deciding on Identifying or Non-Identifying Relationship Looking at the top answers from each question, I appear to get two different ideas of what an identifying relationship is. The first question's response says that an identifying relationship "describes a situation in which the existence of a row in the child table depends on a row in the parent table." An example of this that is given is, "An author can write many books (1-to-n relationship), but a book cannot exist without an author." That makes sense to me. However, when I read the response to question two, I get confused as it says, "if a child identifies its parent, it is an identifying relationship." The answer then goes on to give examples such as SSN (is identifying of a Person), but an address is not (because many people can live at an address). To me, this sounds more like a case of the decision between primary key and non-primary key. My own gut feeling (and additional research on other sites) points to the first question and its response being correct. However, I wanted to verify before I continued forward as I don't want to learn something wrong as I am working to understand database design. Thanks in advance.

  Read the article

• MySQL query puzzle - finding what WOULD have been the most recent date

  - by Hank

  I've looked all over and haven't yet found an intelligent way to handle this, though I feel sure one is possible: One table of historical data has quarterly information: CREATE TABLE Quarterly ( unique_ID INT UNSIGNED NOT NULL, date_posted DATE NOT NULL, datasource TINYINT UNSIGNED NOT NULL, data FLOAT NOT NULL, PRIMARY KEY (unique_ID)); Another table of historical data (which is very large) contains daily information: CREATE TABLE Daily ( unique_ID INT UNSIGNED NOT NULL, date_posted DATE NOT NULL, datasource TINYINT UNSIGNED NOT NULL, data FLOAT NOT NULL, qtr_ID INT UNSIGNED, PRIMARY KEY (unique_ID)); The qtr_ID field is not part of the feed of daily data that populated the database - instead, I need to retroactively populate the qtr_ID field in the Daily table with the Quarterly.unique_ID row ID, using what would have been the most recent quarterly data on that Daily.date_posted for that data source. For example, if the quarterly data is 101 2009-03-31 1 4.5 102 2009-06-30 1 4.4 103 2009-03-31 2 7.6 104 2009-06-30 2 7.7 105 2009-09-30 1 4.7 and the daily data is 1001 2009-07-14 1 3.5 ?? 1002 2009-07-15 1 3.4 && 1003 2009-07-14 2 2.3 ^^ then we would want the ?? qtr_ID field to be assigned '102' as the most recent quarter for that data source on that date, and && would also be '102', and ^^ would be '104'. The challenges include that both tables (particularly the daily table) are actually very large, they can't be normalized to get rid of the repetitive dates or otherwise optimized, and for certain daily entries there is no preceding quarterly entry. I have tried a variety of joins, using datediff (where the challenge is finding the minimum value of datediff greater than zero), and other attempts but nothing is working for me - usually my syntax is breaking somewhere. Any ideas welcome - I'll execute any basic ideas or concepts and report back.

  Read the article

• Issue with creating index organized table

  - by mtim

  I'm having a weird problem with index organized table. I'm running Oracle 11g standard. i have a table src_table SQL> desc src_table; Name Null? Type --------------- -------- ---------------------------- ID NOT NULL NUMBER(16) HASH NOT NULL NUMBER(3) ........ SQL> select count(*) from src_table; COUNT(*) ---------- 21108244 now let's create another table and copy 2 columns from src_table set timing on SQL> create table dest_table(id number(16), hash number(20), type number(1)); Table created. Elapsed: 00:00:00.01 SQL> insert /*+ APPEND */ into dest_table (id,hash,type) select id, hash, 1 from src_table; 21108244 rows created. Elapsed: 00:00:15.25 SQL> ALTER TABLE dest_table ADD ( CONSTRAINT dest_table_pk PRIMARY KEY (HASH, id, TYPE)); Table altered. Elapsed: 00:01:17.35 It took Oracle < 2 min. now same exercise but with IOT table SQL> CREATE TABLE dest_table_iot ( id NUMBER(16) NOT NULL, hash NUMBER(20) NOT NULL, type NUMBER(1) NOT NULL, CONSTRAINT dest_table_iot_PK PRIMARY KEY (HASH, id, TYPE) ) ORGANIZATION INDEX; Table created. Elapsed: 00:00:00.03 SQL> INSERT /*+ APPEND */ INTO dest_table_iot (HASH,id,TYPE) SELECT HASH, id, 1 FROM src_table; "insert" into IOT takes 18 hours !!! I have tried it on 2 different instances of Oracle running on win and linux and got same results. What is going on here ? Why is it taking so long ?

  Read the article

• Database cache that I'm not aware of?

  - by Martin

  I'm using asp.net mvc, linq2sql, iis7 and sqlserver express 2008. I get these intermittent server errors, primary key conflicts on insertion. I'm using a different setup on my development computer so I can't debug. After a while they go away. Restarting iis helps. I'm getting the feeling there is cache somewhere that I'm not aware of. Can somebody help me sort out these errors? Cannot insert duplicate key row in object 'dbo.EnquiryType' with unique index 'IX_EnquiryType'. Edits regarding Venemos answer Is it possible that another application is also accessing the same database simultaneously? Yes there is, but not this particular table and no inserts or updates. There is one other table with which I experience the same problem but it has to do with a different part of the model. How often an in what context do you create a new DataContext instance? Only once, using the singleton pattern. Are the primary keys generated by the database or by the application? Database. Which version of ASP.NET MVC and which version of .NET are you using? RC2 and 3.5.

  Read the article

• How Optimize sql query make it faster

  - by user502083

  Hello every one : I have a very simple small database, 2 of tables are: Node (Node_ID, Node_name, Node_Date) : Node_ID is primary key Citation (Origin_Id, Target_Id) : PRIMARY KEY (Origin_Id, Target_Id) each is FK in Node Now I write a query that first find all citations that their Origin_Id has a specific date and then I want to know what are the target dates of these records. I'm using sqlite in python the Node table has 3000 record and Citation has 9000 records, and my query is like this in a function: def cited_years_list(self, date): c=self.cur try: c.execute("""select n.Node_Date,count(*) from Node n INNER JOIN (select c.Origin_Id AS Origin_Id, c.Target_Id AS Target_Id, n.Node_Date AS Date from CITATION c INNER JOIN NODE n ON c.Origin_Id=n.Node_Id where CAST(n.Node_Date as INT)={0}) VW ON VW.Target_Id=n.Node_Id GROUP BY n.Node_Date;""".format(date)) cited_years=c.fetchall() self.conn.commit() print('Cited Years are : \n ',str(cited_years)) except Exception as e: print('Cited Years retrival failed ',e) return cited_years Then I call this function for some specific years, But it's crazy slowwwwwwwww :( (around 1 min for a specific year) Although my query works fine, it is slow. would you please give me a suggestion to make it faster? I'd appreciate any idea about optimizing this query :) I also should mention that I have indices on Origin_Id and Target_Id, so the inner join should be pretty fast, but it's not!!!

  Read the article

• How to display multiple categories and products underneath each category?

  - by shin

  Generally there is a category menu and each link to a category page where shows all the items under that category. Now I need to show all the categories and products underneath with PHP/MySQL in the same page. So it will be like this. Category 1 description of category 1 item 1 item 2 .. Category 2 description of category 2 item 5 item 6 .. Category 3 description of category 3 item 8 item 9 ... ... I have category and product table in my database. But I am not sure how to proceed. CREATE TABLE IF NOT EXISTS `omc_product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `shortdesc` varchar(255) NOT NULL, `longdesc` text NOT NULL, `thumbnail` varchar(255) NOT NULL, `image` varchar(255) NOT NULL, `product_order` int(11) DEFAULT NULL, `class` varchar(255) DEFAULT NULL, `grouping` varchar(16) DEFAULT NULL, `status` enum('active','inactive') NOT NULL, `category_id` int(11) NOT NULL, `featured` enum('none','front','webshop') NOT NULL, `other_feature` enum('none','most sold','new product') NOT NULL, `price` float(7,2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; CREATE TABLE IF NOT EXISTS `omc_category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `shortdesc` varchar(255) NOT NULL, `longdesc` text NOT NULL, `status` enum('active','inactive') NOT NULL, `parentid` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; I will appreciate your help. Thanks in advance.

  Read the article

• Importing Excel spreadsheet data into existing Access DB

  - by Keeb13r

  I've designed an Access 2003 DB with 3 tables: APPLICATIONS, SERVERS, and INSTALLATIONS. Records in the APPLICATIONS and SERVERS tables are uniquely identified by a synthetic primary key (in Access, an "auto number"). The INSTALLATIONS table is essentially a mapping table between APPLICATIONS and SERVERS: it's a list of records of which applications are installed on which servers. A record in the INSTALLATIONS table is also identified by a synthetic primary key, and it consists of an APPLICATION_ID and SERVER_ID for the records in their respective tables. I have an Excel 2003 spreadsheet I would like to import into this database, but it's proving difficult. The spreadsheet is made up of several tabs/worksheets, each one representing a server with its own listing of installed applications. I'm not sure how to proceed with an import - the "Get External Data -- Import" feature in Access has an import "In an Existing Table" option, but it's greyed out. I'm also unsure how I build the relationships between applications and servers for importing records into the INSTALLATIONS table. I had previously fooled around with adding some security to the Access DB file. I think I removed everything but perhaps I didn't and that's causing the problem? Some sample data from the Excel spreadsheet: SERVER101 * Adobe Reader 9 * BMC Remedy User 7.0 * HostExplorer 2008 * Microsoft Office 2003 * Microsoft Office 2007 * Notepad++ SERVER102 * Adobe Reader 9 * DameWare Mini Remote Control * Microsoft Office 2003 * Microsoft .NET Framework 3.5 SP1 * Oracle 9.2 SERVER103 * AWDView * EXTRA! Personal Client 32-bit * Microsoft Office 2003 * Microsoft .NET Framework 3.5 SP1 * Snagit 9.1 * WinZip 12.1 The Access DB design is very simple: APPLICATION * APPLICATION_ID (autonumber) * APPLICATION_NAME (varchar) SERVER * SERVER_ID (autonumber) * SERVER_NAME (varchar) INSTALLATION * INSTALLATION_ID (autonumber) * APPLICATION_ID (number) * SERVER_ID (number)

  Read the article

• FluentNHibernate: Not.Nullable() doesn't affect output schema

  - by alex

  Hello I'm using fluent nhibernate v. 1.0.0.595. There is a class: public class Weight { public virtual int Id { get; set; } public virtual double Value { get; set; } } I want to map it on the following table: create table [Weight] ( WeightId INT IDENTITY NOT NULL, Weight DOUBLE not null, primary key (WeightId) ) Here is the map: public class WeightMap : ClassMap<Weight> { public WeightMap() { Table("[Weight]"); Id(x => x.Id, "WeightId"); Map(x => x.Value, "Weight").Not.Nullable(); } } The problem is that this mapping produces table with nullable Weight column: Weight DOUBLE null Not-nullable column is generated only with default convention for column name (i.e. Map(x = x.Value).Not.Nullable() instead of Map(x = x.Value, "Weight").Not.Nullable()), but in this case there will be Value column instead of Weight: create table [Weight] ( WeightId INT IDENTITY NOT NULL, Value DOUBLE not null, primary key (WeightId) ) I found similiar problem here: http://code.google.com/p/fluent-nhibernate/issues/detail?id=121, but seems like mentioned workaround with SetAttributeOnColumnElement("not-null", "true") is outdated. Does anybody encountered with this problem? Is there a way to specify named column as not-nullable?

  Read the article

• Selectively search and replace certain lines using a regular expression

  - by eneveu

  I have a file containing a lot of SQL statements, such as: CREATE TABLE "USER" ( "ID" INTEGER PRIMARY KEY, "NAME" CHARACTER VARYING(50) NOT NULL, "AGE" INTEGER NOT NULL ); COPY "USER" (id, name, age) FROM stdin; 1 Skywalker 19 2 Kenobi 57 I want the column names in the COPY statements to be uppercased and quoted: COPY "USER" ("ID", "NAME", "AGE") FROM stdin; Using sed, I found the following regexp: sed -r 's/([( ])(\w+)([,)])/\1"\U\2\E"\3/g' It does replace the column names, but it is not selective enough, and replaces other words in the file: ~/test]$sed -r 's/([( ])(\w+)([,)])/\1"\U\2\E"\3/g' star_wars_example CREATE TABLE "USER" ( "ID" INTEGER PRIMARY "KEY", "NAME" CHARACTER VARYING("50")NOT "NULL", "AGE" INTEGER NOT NULL ); COPY "USER" ("ID", "NAME", "AGE") FROM stdin; 1 Skywalker 19 2 Kenobi 57 To avoid this problem, I want sed to only apply my regexp to the lines starting with COPY and ending with FROM stdin;. I have looked into lookahead / lookbehind, but they are not supported in sed. They seem to be supported in super-sed, but I am currently using Cygwin (Windows is mandatory here...) and it does not seem available in the package list. Is there a way to force sed to only consider specific line? I've considered piping my file through grep before applying sed, but other lines will then disappear from the output. Am I missing something obvious? It would be great if the answer was easily applicable on a default Cygwin install. I guess I could try installing super-sed on cygwin, but I'd like to know if there are more obvious ideas

  Read the article

< Previous Page | 297 298 299 300 301 302 303 304 305 306 307 308  | Next Page >