[how to] I want to know the defference between using varchar(100) and varchar(255)?

---

• I want to know the defference between using varchar(100) and varchar(255)?
• How to check for mutiple rows with same data?
• What is the difference between optimize table and analyze table in mysql
• Select multiple columns into a single output column
• mysql alternative to prepared statements in triggers
• Maintenance in MYSQL when innodb_file_per_table disabled
• Does disabling index exists in a table?
• Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?
• Display user-defined types and their details
• Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP
• Suggestion for storing a lot of simple data
• slow queries - set weight to token type in tsquery - postgresql
• Special ordering
• What are the advantages of a database design with single table for MtM relationships?
• Why is SQL Server memory showing in task manager
• Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?
• How to solve index missing problem in one table, while other table of same property does not produce any error?
• Is data retrieved from SQL Server compressed for transmission?
• Can this array group count query be improved?
• Oracle 10g slow performance on first query
• MySQL Workbench sync keeps requesting the same changes
• Getting max values from MySQL tables
• Rules in SQL Server 2005
• FLUSH TABLES stays in the processlist even after execution
• SQL Server Analysis Studio naive Bayes attribute has too many states
• How could DBAs be more 'programmer friendly'?

I want to know the defference between using varchar(100) and varchar(255)? Posted: 26 Feb 2013 08:17 PM PST I want to know defferents between varchar(100) and varchar(255) in terms of memory usege and programming problem of using more memory please help me

How to check for mutiple rows with same data? Posted: 26 Feb 2013 08:11 PM PST Hi all can anyone advise how do i check mutiple rows with same data? E.g. i have 2 rows with following values: Cola Colb Colc TYPE_APPLE 123 abc Colb Colb Colc TYPE_APPLE 123 abc I want to write an sql query which will not select data of mutiple rows which contain the same data as above. How should i write the query?

What is the difference between optimize table and analyze table in mysql Posted: 26 Feb 2013 07:33 PM PST What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.

Select multiple columns into a single output column Posted: 26 Feb 2013 04:19 PM PST I have the following query that sort of works, but I find it to be kludgy and inefficient, and it outputs blobs for most of the data fields which isn't what I want. I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic. We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql. The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in. Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line. The fields all contain the same type of data. SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData' SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table UNION ALL SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table UNION ALL SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table UNION ALL SELECT PN4, Mfg, '26', 'PN4', OtherData INTO OUTFILE 'XXX.dat' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM Table d; Input looks like the following table: | PN1 | PN2 | PN3 | PN4 | MFG | TableId | OtherData | ----------------------------------------------------------------------- | asdf | abde | 12354 | asdfc | 2 | 26 | 0.2456 | | nupo | 1354 | null | null | 2 | 26 | 1.53 | | ... | ... | ... | ... | ... | ... | ... | | ... | ... | ... | ... | ... | ... | ... | | ... | ... | ... | ... | ... | ... | ... | I want the output .dat file to look like this: "Pn","Mfg","TableId","SourceColumn","OtherData" "asdf",2,26,"PN1",0.2456 "abde",2,26,"PN2",0.2456 "12354",2,26,"PN3",0.2456 "asdfc",2,26,"PN4",0.2456 "nupo",2,26,"PN1",1.53 "1354",2,26,"PN2",1.53 ... ... ... However, what I currently get looks like this: | Pn | Mfg | TableId | SourceColumn | OtherData | ----------------------------------------------------------------- | "Pn" | {blob} | "TableId" | "PN" | {blob} | | "asdf" | {blob} | "PN1" | "PN1" | {blob} | | "abde" | {blob} | "PN2" | "PN2" | {blob} | | "12354" | {blob} | "PN3" | "PN3" | {blob} | | "asdfc" | {blob} | "PN4" | "PN4" | {blob} | | "nupo" | {blob} | "PN1" | "PN1" | {blob} | | "1354" | {blob} | "PN2" | "PN2" | {blob} | | ... | ... | ... | ... | ... | | ... | ... | ... | ... | ... | | ... | ... | ... | ... | ... |

mysql alternative to prepared statements in triggers Posted: 26 Feb 2013 02:11 PM PST I'm trying to get some stored procedures to fire from a MySQL 5.1 trigger. When these were called without triggers, I used prepare and execute statements to build queries and other SQL calls. I have a servies of statements like these: select concat("select ", $var1, " from ". $var2, " where col1 = ", $var3) into @sql; prepare s1 from @sql; execute s1; Clearly that approach won't work with code involved in a trigger as prepared statements aren't allowed in triggers or the procedures they call. I'm looking for some alternative ideas about how to build statements like these and execute them without using dynamic SQL. I've thought about nested case statements and similar structures, but $var1 and $var3 could have any of hundreds of values. Has anyone found a way to do this? Googling hasn't been much help.

Maintenance in MYSQL when innodb_file_per_table disabled Posted: 26 Feb 2013 01:04 PM PST I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace. But what if I need to perform index maintenance on InnoDB tables with ANALYZE command, it will grow the single tablespace also? What other alternatives are there to increase performance or doing some maintenance in Innodb engine, when using a single tablespace and without grow out of control the single ibdata1. Regards. Matthew

Does disabling index exists in a table? Posted: 26 Feb 2013 12:00 PM PST So I have a script that runs daily. It creates a temp table then inserts the records to a table from the temp table. The script used to create indexes but few months ago I commented out the index creation. My question is, does the index still exist in the table? Will it still slow down the update, insert etc? I can see the index names in the index usage report.

Why would I NOT use the SQL Server option "optimize for ad hoc workloads"? Posted: 26 Feb 2013 11:34 AM PST I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why is there even an option to "optimize for ad hoc workloads"? Shouldn't this always be on? Whether the developers are using ad-hoc SQL or not, why would you not have this option enabled on every instance that supports it (SQL 2008+), thereby reducing cache bloat?

Display user-defined types and their details Posted: 26 Feb 2013 07:28 PM PST I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.

Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP Posted: 26 Feb 2013 06:43 PM PST I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment. I have a web based form with multiple fields including a field that is hidden on the form that writes the CURRENT_TIMESTAMP to the database when the form is submitted. The challenge I'm facing is that I want to create reports from these forms (sorting by month, week, or year) and order them by the timestamp but based on what I've seen here, sorting is not possible. Is there an alternative I can use to achieve the results I want?

Suggestion for storing a lot of simple data Posted: 26 Feb 2013 11:56 AM PST I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables Table: Pages ID URL - Max Length = 2048 chars ---------- ------------------------------- 1 http://www.site1.com/page.php 2 http://www.site2.com/page-abc.php 3 http://www.site3.com/page-1.php 4 http://www.site4.com/page-cd.php 5 http://www.site5.com/page-nice.php 6 http://www.site6.com/page-some.php 7 http://www.site7.com/page-hrmm.php 8 http://www.site8.com/page-stack.php 9 http://www.site9.com/page-ex.php 10 http://www.site10.com/page-dba.php Table: Links Page Links ---------- ------------------------------- 2 1 3 1 4 1 5 1 6 1 7 1 8 1 8 9 9 1 10 1 Basically I'll be able to see what webpages link to where recurrently/several levels deep per website. I want to map a large network of websites and their linking patterns. So I need to know if there is a better way of doing so, and maybe some suggestions on how to design the database structure/system. I was planning on PostgreSQL to start with since I've used it some, but with this amount of data I'm open to anything.

slow queries - set weight to token type in tsquery - postgresql Posted: 26 Feb 2013 04:33 PM PST Postgresql version 9.2.3! I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm trying now with 100 entries), which I first put in a query table, create a tsquery column of the names and index it with gist. Main table structure: CREATE TABLE IF NOT EXISTS pubchem_compounds_synonyms_all ( cid int NOT NULL references pubchem_compounds_index(cid) ON UPDATE CASCADE ON DELETE CASCADE, name VARCHAR(2000) NOT NULL, synonym VARCHAR(2000) NOT NULL, PRIMARY KEY (cid, name, synonym) ); CREATE TEMPORARY TABLE synonyms_temp AS SELECT * FROM pubchem_compounds_synonyms_all; COPY synonyms_temp FROM '...' delimiters E'\t'; INSERT INTO pubchem_compounds_synonyms_all SELECT DISTINCT * FROM synonyms_temp; I was trying different approaches, thus for testing gin index I created a clone: CREATE TABLE synonyms_all_gin_tsvcolumn AS SELECT * FROM pubchem_compounds_synonyms_all; then: ALTER TABLE synonyms_all_gin_tsvcolumn ADD COLUMN tsv_syns TSVECTOR; UPDATE synonyms_all_gin_tsvcolumn SET tsv_syns = to_tsvector(synonym); CREATE INDEX gin_tsv_colum_index ON synonyms_all_gin_tsvcolumn USING GIN (tsv_syns); The query table is: CREATE TABLE IF NOT EXISTS cmap_names_query ( name VARCHAR (2000) PRIMARY KEY NOT NULL ); Same as in the main table, I fill it in with COPY from via a temp table and then I add the tsquery column: ..... ALTER TABLE cmap_names_query ADD COLUMN lexemes TSQUERY; UPDATE cmap_names_query SET lexemes = plainto_tsquery(name); CREATE INDEX cmap_gist_tsquery_index ON cmap_names_query USING gist (lexemes tsquery_ops); The query is basically a join between both tables: SELECT DISTINCT ON (c.lexemes) c.name, s.cid, s.synonym, ts_rank(s.tsv_syns,c.lexemes) FROM synonyms_all_gin_tsvcolumn s JOIN cmap_names_query c ON c.lexemes @@ s.tsv_syns; lexemes is the gist indexed tsquery column on my query table, whereas tsv_syns is the gin indexed tsvector column in the main names table, the one with 91 million records. The query is intended to match names, exact matches if possible. It works very well for such a large table. Normal names, containing only characters, can be retreated even in microseconds. The problem is when the string names contains numbers. The operation tsvector and tsquery create one token for each number, and all together makes the query for this sort of entries rather slow, well, slower. Instead of a few milliseconds, they take aprox 1-2 seconds each. I would like to reduce this query time to a few milliseconds like the other entries, but I don't know how. I have tested it with and without ts_rank to find out that ranking only add half a second to the total query, if it even makes a difference. so that's not my problem Some samples queries are: query: Limit (cost=32.13..36.14 rows=1 width=92) (actual time=40.001..40.002 rows=1 loops=1) Buffers: shared hit=201 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.13..100.24 rows=17 width=92) (actual time=39.998..39.998 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('10-methoxyharmalan'::text)) Buffers: shared hit=201 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.13 rows=17 width=0) (actual time=39.984..39.984 rows=1 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('10-methoxyharmalan'::text)) Buffers: shared hit=200 Total runtime: 40.037 ms result: (cid |name|synonym|tsv vector) 5474706 | 6-Methoxyharmalan | 10-Methoxyharmalan | '10':1 'methoxyharmalan':2 query: Limit (cost=32.23..36.23 rows=1 width=92) (actual time=2215.127..2215.128 rows=1 loops=1) Buffers: shared hit=10086 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.23..148.34 rows=29 width=92) (actual time=2215.125..2215.125 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('1,4-chrysenequinone'::text)) Buffers: shared hit=10086 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.22 rows=29 width=0) (actual time=2215.108..2215.108 rows=1 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('1,4-chrysenequinone'::text)) Buffers: shared hit=10085 Total runtime: 2215.182 ms result: 180933 | 1,4-Chrysenedione | 1,4-Chrysenequinone | '1':1 '4':2 'chrysenequinon':3 query: Limit (cost=32.00..36.02 rows=1 width=92) (actual time=2852.934..2852.936 rows=1 loops=1) Buffers: shared hit=7292 -> Bitmap Heap Scan on synonyms_all_gin_tsvcolumn (cost=32.00..36.02 rows=1 width=92) (actual time=2852.930..2852.930 rows=1 loops=1) Recheck Cond: (tsv_syns @@ plainto_tsquery('2-deoxy-D-glucose'::text)) Buffers: shared hit=7292 -> Bitmap Index Scan on gin_tsv_colum_index (cost=0.00..32.00 rows=1 width=0) (actual time=2852.897..2852.897 rows=121 loops=1) Index Cond: (tsv_syns @@ plainto_tsquery('2-deoxy-D-glucose'::text)) Buffers: shared hit=7291 Total runtime: 2852.992 ms result: 40 | 2-deoxy-D-glucose | 2-deoxy-D-glucose | '2':1 'd':4 'deoxi':3 'deoxy-d-glucos':2 'glucos':5 I wonder what the best way to make this last queries faster would be. I have tried with a pre-processing script that removes all the numbers, it speeds up the search up to 3 seconds in total, but I miss the exact/closest match that I was looking for in some of the cases, so that's no use. Other approaches that came to mind where: Sol 1: assign priority weight to word tokens in contrast to numeric ones I think this could be a potential good solution for me, but as far as I have seen cannot be done. Tsvectors/queries can be labelled, but not token types or IS THERE A WAY TO LABEL TOKENS DIFFERENTLY WITHIN THE SAME TSVECTOR? Sol 2: create a new configuration and delete numeric mappings with ALTER FULLTEXTMAPPING or add numbers and characters like - ( ) to the stopword list Same as the parser, it might lead me to wrong matches, although since it keeps the positional information it me perform good. I'm not sure how i should do this though. My postgres.conf parameters: shared_buffers = 24GB checkpoint_segments=512 effective_cache_size=72GB work_mem = 1GB system specif. are: 4 x Intel X7550 (@2.00GHz) (total 32 Cores), 256GB Memory, CentOS 5.6 (64 bit), 18TB local disk (SATA-RAID) kernel.shmmax=64GB kernel.shmall=4GB I have tried lower amounts of shared_buffer and effective_cache_size (16GB and 32GB respectively), no difference in performance from the current one, so I'm planing to change it back to those limits I tried a gist index on querytree lexemes, didn't make much difference I'm a little bit lost and I would appreciate any ideas or possible solutions to speed up my queries. Thanks :) PD: Any recommendations for nonSQL DBs that could improve performance?

Special ordering Posted: 26 Feb 2013 12:40 PM PST I have a hierarchical query but I don't manage to order it as I want. I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly. This a lookalike of the information I have: ID | Sequence | Parent_ID | text 145 | 1 | NULL |'1. INFORMATION' 146 | 2 | NULL |'2. MORE INFORMATION' 147 | 3 | NULL |'3. EVEN MORE' 148 | 1 | 147 |' 1. INFORMATION LVL2 FOR PARENT 3' 149 | 2 | 147 |' 2. INFORMATION LVL2 FOR PARENT 3' 150 | 4 | NULL |'4. EVEN MORE' 151 | 1 | 146 |' 1. INFORMATION LVL2 FOR PARENT 2' If I run the query without ordering I end up with information like this: ID | Sequence | Parent_ID | text 148 | 4 | NULL |'4. EVEN MORE' 145 | 1 | NULL |'1. INFORMATION' 147 | 3 | NULL |'3. EVEN MORE' 149 | 1 | 147 |' 1. INFORMATION LVL2 FOR PARENT 3' 150 | 2 | 147 |' 2. INFORMATION LVL2 FOR PARENT 3' 146 | 2 | NULL |'2. MORE INFORMATION' 151 | 1 | 146 |' 1. INFORMATION LVL2 FOR PARENT 2' currently I have in place an ORDER BY id, sequence but that sends the second level item of the parent 146 to the last position looking just like the first set of data. is there a way to order it to put that lost node 151 under its parent 146 without messing up all the other information? This is more or less what I need as end result: ID | Sequence | Parent_ID | text 145 | 1 | NULL |'1. INFORMATION' 146 | 2 | NULL |'2. MORE INFORMATION' 151 | 1 | 146 |' 1. INFORMATION LVL2 FOR PARENT 2' 147 | 3 | NULL |'3. EVEN MORE' 148 | 1 | 147 |' 1. INFORMATION LVL2 FOR PARENT 3' 149 | 2 | 147 |' 2. INFORMATION LVL2 FOR PARENT 3' 150 | 4 | NULL |'4. EVEN MORE'

What are the advantages of a database design with single table for MtM relationships? Posted: 26 Feb 2013 10:43 AM PST The database that backs our software product has a table design like the following: Positions ID Name Parts 1 One 12345 2 Two 12346 3 Three 12347 Collections ID TableID Collection 12345 1;1 1;2 12346 1;1 3;4 12347 1;2;2 5;1;2 Parts ID Name 1 TestOne 2 TestTwo 3 TestThree 4 TestFour 5 TestFive SubParts 1 SubPartOne 2 SubPartOne From the above example, each Position has a collection of Parts, but these are mapped generically (without foreign key constraints) into the Collections table. The Collections table keeps track of all relationships between all objects, not just between the example tables shown above, and will be used any time a collection is used. That means that if I want to get the Position with ID 1, and all of its parts. I have to do three queries: SELECT * FROM Positions WHERE ID = 1 SELECT * FROM Collections WHERE ID = 12345 Split the string by the semicolons SELECT * FROM Parts WHERE ID = 1 OR ID = 2 The advantages of the current approach are: A collection of something can actually be selected from more than table. For example, if SubPart inherits from Part, and position contains a list of Parts, this will be handled OK. The disadvantages of the current approach are: Speed? We need to do many queries to load data. The Collections table is the largest or second largest table in our database. No support from ORM frameworks? We are looking at switching our persistency layer to use EF or NHibernate etc. but I don't believe our current design will be supported by any of these frameworks. My questions are: Is there advantages or disadvantages to this approach that I haven't listed? Is this design familiar to people and if so, does it have a name? If the design is familiar, is it supported by out of the box ORM frameworks?

Why is SQL Server memory showing in task manager Posted: 26 Feb 2013 08:11 PM PST I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that someone else has setup. I am expecting to see sqlservr.exe running at about 40mb memory. However, Task manager is displaying that over 4Gb of memory is being used. Is there a setting in SQL Server that is causing the task manager to report this inaccurately? Or is there something going wrong in the background of my SQL Server that is causing it to balloon?

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Posted: 26 Feb 2013 01:56 PM PST Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Conceptually, I was thinking something like: mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :) Thanks for your time!

How to solve index missing problem in one table, while other table of same property does not produce any error? Posted: 26 Feb 2013 03:49 PM PST Two databases with same structure at least for my eye. One of them was used as training/testing, where I can add list of products in Art table via a program's extension FDT but not in Art table of another database. The databases have following properties. EXEC [fakt000].sys.sp_addextendedproperty @name=N'user15', @value=N'sysadmin' GO ---Extra line of code in error producing DB, I am using user:sa to --login SET COMPATIBILITY_LEVEL = 80 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [dbo].[sp_fulltext_database] @action = 'disable' end GO common properties in both DB 999 and DB 000 SET ANSI_NULL_DEFAULT OFF GO SET ANSI_NULLS OFF GO SET ANSI_PADDING OFF GO SET ANSI_WARNINGS OFF GO SET ARITHABORT OFF GO SET AUTO_CLOSE OFF GO SET AUTO_CREATE_STATISTICS ON GO SET AUTO_SHRINK OFF GO SET AUTO_UPDATE_STATISTICS ON GO SET CURSOR_CLOSE_ON_COMMIT OFF GO SET CURSOR_DEFAULT GLOBAL GO SET CONCAT_NULL_YIELDS_NULL OFF GO SET NUMERIC_ROUNDABORT OFF GO SET QUOTED_IDENTIFIER OFF GO SET RECURSIVE_TRIGGERS OFF GO SET DISABLE_BROKER GO SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO SET DATE_CORRELATION_OPTIMIZATION OFF GO SET TRUSTWORTHY OFF GO SET ALLOW_SNAPSHOT_ISOLATION OFF GO SET PARAMETERIZATION SIMPLE GO SET READ_COMMITTED_SNAPSHOT OFF GO SET HONOR_BROKER_PRIORITY OFF GO SET READ_WRITE GO SET RECOVERY SIMPLE GO SET MULTI_USER GO SET PAGE_VERIFY TORN_PAGE_DETECTION GO SET DB_CHAINING OFF GO The program generates a error message saying table Art misses index when I use fakt000 as a destination database, while using fakt999 as database I have no problems. I do have checked the table Products in both the databases seems similar in name of indexes. To solve this issue I have rebuilt all the indexes in fakt000.dbo.Art still no luck. I do have change the collation to be similar and again rebuilt all indexes in fakt000.dbo.Art I do have added same tables in fakt000 but not in fakt999 database. I do not think this would cause any of such error, as error states fakt000.dbo.Art missing index I have ran profiler to see what commands do the program use and where it may have missed, seems too complex to me, I have enlisted it down, Select * from version go exec sp_provider_types_rowset NULL,NULL go exec [Fakt000]..sp_tables_rowset;2 NULL,NULL go exec [Fakt000]..sp_indexes_rowset N'Art',NULL,NULL go exec [Fakt000]..sp_indexes_rowset N'ArtPris',NULL,NULL go SET LOCK_TIMEOUT 10000 go select serverproperty(N'instancename') --here seems to arise the problem go select ServerProperty('ErrorLogFileName')-- may be here, I am not sure what! go create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int) insert #err_log_tmp exec master.dbo.sp_enumerrorlogs SELECT er.ArchiveNo AS [ArchiveNo], CONVERT(datetime, er.CreateDate, 101) AS [CreateDate] FROM #err_log_tmp er ORDER BY [ArchiveNo] ASC drop table #err_log_tmp go create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int) insert #err_log_tmp exec master.dbo.sp_enumerrorlogs 2 SELECT er.ArchiveNo AS [ArchiveNo], CONVERT(datetime, er.CreateDate, 101) AS [CreateDate] FROM #err_log_tmp er ORDER BY [ArchiveNo] ASC drop table #err_log_tmp go select serverproperty(N'instancename') go select ServerProperty('ErrorLogFileName') go EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc' go I am not sure what to do next? to solve it out. I can insert some products into table using insert but not from program. Profiler gives me these codes when it successes in test DB fakt999 exec [Fakt999]..sp_tables_rowset;2 NULL,NULL go exec [Fakt999]..sp_indexes_rowset N'Art',NULL,NULL go exec [Fakt999]..sp_indexes_rowset N'ArtPris',NULL,NULL go exec sp_provider_types_rowset NULL,NULL go exec [Fakt999]..sp_tables_rowset;2 NULL,NULL go exec [Fakt999]..sp_columns_rowset N'Art',NULL,NULL go Select * From Art Where Artikelnr='$MALL$' go declare @p1 int set @p1=2 exec sp_prepexec @p1 output,N'@P1 nvarchar(30)' ,N'Select Apris,inpris,Medelpris,Ändrad,Låstinpris,Sortimentstatus ,VariantRadTyp,VariantKolTyp from art Where Artikelnr=@P1',N'73974' select @p1 go Select * from ArtPris Where Artikelnr='73974' go Select * from artlev where artikelnr='73974' go UPDATE Art SET ArtikelNr = '73974', RevideradDag = '2013-02-25' , Ändrad = 9 WHERE ((Artikelnr='73974')) go exec sp_unprepare 2 go NOTE:The program FDT is a ERP software whose source code or support is out of scope. *Edit: Sorry I may have been confused myself in writing the name of table in both databases as products instead of **Art**.*

Is data retrieved from SQL Server compressed for transmission? Posted: 26 Feb 2013 04:21 PM PST Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it? I'm examining analysis tools, and the volume of data can take minutes to transmit over our network. I'm wondering whether I should expect a performance increase if we pull data from a compressed data store on the same remote server. As long as we're on the topic, I'm curious: is data transmitted in binary or ASCII? For example, if the value 12345 is queried from an INT column, is it transmitted as the five bytes 0x31, 0x32, 0x33, 0x34, 0x35; the two bytes that are required for the value; or four bytes as required for the column? To be clear, I understand that there are options regarding storing data with compression, and backing it up. I'm asking about how data is transmitted.

Can this array group count query be improved? Posted: 26 Feb 2013 06:07 PM PST So I have the following query explain analyze with tags as ( select unnest(tags) as tag_name from tasks where user_id = 1 ) select count(9), tag_name from tags group by tag_name order by count(9) desc limit 50 Gives me the following result: Limit (cost=3243.86..3243.99 rows=50 width=32) (actual time=2.278..2.278 rows=1 loops=1) CTE tags -> Bitmap Heap Scan on tasks (cost=12.35..1917.72 rows=52700 width=13) (actual time=0.098..2.074 rows=261 loops=1) Recheck Cond: (user_id = 1) -> Bitmap Index Scan on index_tasks_user_id (cost=0.00..12.22 rows=527 width=0) (actual time=0.065..0.065 rows=261 loops=1) Index Cond: (user_id = 1) -> Sort (cost=1326.14..1326.64 rows=200 width=32) (actual time=2.278..2.278 rows=1 loops=1) Sort Key: (count(9)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=1317.50..1319.50 rows=200 width=32) (actual time=2.273..2.274 rows=1 loops=1) -> CTE Scan on tags (cost=0.00..1054.00 rows=52700 width=32) (actual time=0.099..2.177 rows=261 loops=1) Total runtime: 2.314 ms Which is pretty decent I suppose. The previous way of doing things where to have a bunch of join tables and that gave me something like below: Limit (cost=919.38..919.40 rows=50 width=12) (actual time=163.164..163.257 rows=50 loops=1) -> Sort (cost=919.38..919.48 rows=206 width=12) (actual time=163.162..163.194 rows=50 loops=1) Sort Key: (count(*)) Sort Method: top-N heapsort Memory: 28kB -> HashAggregate (cost=917.39..918.01 rows=206 width=12) (actual time=162.899..163.008 rows=132 loops=1) -> Nested Loop (cost=456.90..917.19 rows=206 width=12) (actual time=1.040..162.361 rows=416 loops=1) -> Hash Join (cost=456.90..904.32 rows=206 width=4) (actual time=1.029..159.429 rows=416 loops=1) Hash Cond: (taggings.workout_id = workouts.id) -> Seq Scan on taggings (cost=0.00..416.64 rows=40214 width=8) (actual time=0.010..45.753 rows=37029 loops=1) -> Hash (cost=455.91..455.91 rows=282 width=4) (actual time=1.004..1.004 rows=293 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Bitmap Heap Scan on workouts (cost=4.49..455.91 rows=282 width=4) (actual time=0.101..0.744 rows=293 loops=1) Recheck Cond: (user_id = 1) -> Bitmap Index Scan on index_workouts_on_user_id (cost=0.00..4.48 rows=282 width=0) (actual time=0.058..0.058 rows=293 loops=1) Index Cond: (user_id = 1) -> Index Scan using tags_pkey on tags (cost=0.00..0.06 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=416) Index Cond: (id = taggings.tag_id) Total runtime: 163.393 ms Now forget about the last explain and lets focus on the first one. Can it be optimized further? Any tricks or such that I might be missing out on? I guess an index on the user_id column should be plenty for this query?

Oracle 10g slow performance on first query Posted: 26 Feb 2013 08:28 PM PST We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated. The main issue is windows taking ages (minutes) to open because the initial query to fill their list boxes is taking so long. The second time a user opens the window its fine. We would expect this to be application cacheing but we don't think so. We see similar problems using Crystal Reports and using command line SQL queries. We have increased the RAM available to the database. Another part of our business has a slightly different version of the same application and database and doesn't get this problem. The time to complete a query can be 30 minutes for the first time in a session and 30 seconds for subsequent executions.

MySQL Workbench sync keeps requesting the same changes Posted: 26 Feb 2013 04:28 PM PST I am using MySQL Workbench, and when I try to "synchronize" it with my remote database, it keeps detecting some changes to make. Specifically, the most recurrent ones are: Dropping foreign keys and re-creating the same ones again Changing the collation I was compliant and executed all the queries given to me (and added the semi-colon that they forgot). MySQL didn't complain and executed them. However it didn't help, I can run it 20 times in a row, it will still ask the same useless changes.

Getting max values from MySQL tables Posted: 26 Feb 2013 05:28 PM PST I have three tables: competitions (id, name, date) athletes (id,name) results (place, id_athlete, id_competition, ranking_points) where: results.id_athlet=athlet.id results.id_competition=competitions.id I need a query to select the latest ranking_points of each athlete based on competitions.date.

Rules in SQL Server 2005 Posted: 26 Feb 2013 10:27 AM PST The syntax for rules in SQL Server 2005 is given: CREATE RULE [ schema_name . ] rule_name AS condition_expression Whenever using rule, for example: CREATE RULE list_rule AS @list IN ('1389', '0736', '0877'); Why are we using a variable like @list in it? It doesn't correspond to the table name or anything. Is that used for storing the values given in the IN statement or anything else? I just need an explanation for this. I know that rules are now deprecated but I am using SQL Server 2005.

FLUSH TABLES stays in the processlist even after execution Posted: 26 Feb 2013 06:28 PM PST Here is my code from Stored Procedures in Mysql FLUSH TABLES; TRUNCATE TABLE tbl_cdrload; TRUNCATE TABLE tbl_cdrdetails_temp; Then i execute a query to load a file in to database as below LOAD DATA LOCAL INFILE filename INTO TABLE tbl_cdrload FIELDS TERMINATED BY LINES TERMINATED BY '\n'; I executed this set of code one after the other several times using a Windows service. After some time, the Windows service stops automatically. When I open MySQL Administrator, I could see the FLUSH TABLES still running in the processlist. Can anyone explain why so and if this is the reason for the service to stop?

SQL Server Analysis Studio naive Bayes attribute has too many states Posted: 26 Feb 2013 03:57 PM PST I'm using SQL Server Analysis Studio and I'm trying to train a naive Bayes classifier. I've got an attribute called "item description" that basically represents products you can buy at a store. I've got these products mapped to a code and I want to train a classifier to take products it hasn't seen before and give me the code of highest probability. What I'm running into is that my attribute of "item description" has too many states and so SSAS is throwing away a lot of my items - Analysis Studio says so. I'm got a case table that has an ID and code and a nested table that represents these "item descriptions" chopped up into individual words. Naive Bayes then uses conditional probabilities to map the words to a code. I tinkered with the MAXIMUM_STATES setting and set it to 0 but according to the documentation, it maxes out at 65k. Does anyone know if it's possible to raise this limit some magical way? Or can anyone suggest to me an alternate implementation of a naive Bayes classifier that is more scaleable.

How could DBAs be more 'programmer friendly'? Posted: 26 Feb 2013 01:57 PM PST The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing about the divide between DBAs and programmers in some workplaces. What could DBAs do differently to work better with programmers on issues like this? Should we: Study the tools and languages our programmers are using to understand their difficulties they face, particularly when working with well designed databases? Encourage programmers to be better educated about databases and the advantages of having business logic at the database level? Change the way we define interfaces to our data - such as by using more programmer friendly transactional APIs (eg for issues such as backwards compatibility)?

You are subscribed to email updates from Recent Questions - Database Administrators - Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610