Sponsored Content
Full Discussion: Optimizing query
Special Forums UNIX and Linux Applications Optimizing query Post 302130445 by kahuna on Monday 6th of August 2007 09:06:11 AM
Old 08-06-2007
I made a test table with a single varchar field and no index. I loaded it with 30,000 records and an additional 30 duplicate records. I ran the following queries.

Code:
select count(*) FROM tableA
WHERE rowid not in
(SELECT MIN(rowid) FROM tableA GROUP BY column1);

1 hour 12 minutes 58 seconds

Code:
select count(*) FROM tableA A1
WHERE column1 in (SELECT column1 FROM tableA GROUP BY column1 having count(*)
> 1)
and rowid != (select min(rowid) from tableA A2 where A1.column1 = A2.column1);

1 second

Code:
select count(*) from tableA a
     where rowid <> ( select max(rowid)
                        from tableA b
                       where b.column1 = a.column1);

6 min 29 sec

Your mileage may vary.

Last edited by kahuna; 08-06-2007 at 11:04 AM..
 

10 More Discussions You Might Find Interesting

1. Filesystems, Disks and Memory

Optimizing the system reliability

My product have around 10-15 programs/services running in the sun box, which together completes a task, sequentially. Several instances of the each program/service are running in the unix box, to manage the load and for risk-management reasons. As of now, we dont follow a strict strategy in... (2 Replies)
Discussion started by: Deepa
2 Replies

2. Filesystems, Disks and Memory

optimizing disk performance

I have some questions regarding disk perfomance, and what I can do to make it just a little (or much :)) more faster. From what I've heard the first partitions will be faster than the later ones because tracks at the outer edges of a hard drive platter simply moves faster. But I've also read in... (4 Replies)
Discussion started by: J.P
4 Replies

3. Shell Programming and Scripting

Optimizing for a Speed-up

How would one go about optimizing this current .sh program so it works at a more minimal time. Such as is there a better way to count what I need than what I have done or better way to match patterns in the file? Thanks, #declare variables to be used. help=-1 count=0 JanCount=0 FebCount=0... (3 Replies)
Discussion started by: switch
3 Replies

4. OS X (Apple)

Optimizing OSX

Hi forum, I'm administrating a workstation/server for my lab and I was wondering how to optimize OSX. I was wondering what unnecessary background tasks I could kick off the system so I free up as much memory and cpu power. Other optimization tips are also welcome (HD parameters, memory... (2 Replies)
Discussion started by: deiphon
2 Replies

5. Shell Programming and Scripting

Optimizing the code

Hi, I have two files in the format listed below. I need to find out all values from field 12 to field 20 present in file 2 and list them in file3(format as file2) File1 : FEIN,CHRISTA... (2 Replies)
Discussion started by: nua7
2 Replies

6. Shell Programming and Scripting

Optimizing awk script

Can this awk statement be optimized? i ask because log.txt is a giant file with several hundred thousands of lines of records. myscript.sh: while read line do searchterm="${1}" datecurr=$(date +%s) file=$(awk 'BEGIN{split(ARGV,var,",");print var}' $line) ... (3 Replies)
Discussion started by: SkySmart
3 Replies

7. Shell Programming and Scripting

Optimizing search using grep

I have a huge log file close to 3GB in size. My task is to generate some reporting based on # of times something is being logged. I need to find the number of time StringA , StringB , StringC is being called separately. What I am doing right now is: grep "StringA" server.log | wc -l... (4 Replies)
Discussion started by: Junaid Subhani
4 Replies

8. Shell Programming and Scripting

Optimizing find with many replacements

Hello, I'm looking for advice on how to optimize this bash script, currently i use the shotgun approach to avoid file io/buffering problems of forks trying to write simultaneously to the same file. i'd like to keep this as a fairly portable bash script rather than writing a C routine. in a... (8 Replies)
Discussion started by: f77hack
8 Replies

9. Shell Programming and Scripting

Optimizing bash loop

now, i have to search for a pattern within a particular time frame which the user will provide in the following format: 19/Jun/2018:07:04,21/Jun/2018:21:30 it is easy to get tempted to attempt this search with a variation of the following awk command: awk... (3 Replies)
Discussion started by: SkySmart
3 Replies

10. Web Development

Optimizing JS and CSS

Yes. Got few suggestions. - How about minifying resources - mod_expires - Service workers setup https://www.unix.com/attachments/web-programming/7709d1550557731-sneak-preview-new-unix-com-usercp-vuejs-demo-screenshot-png (8 Replies)
Discussion started by: Akshay Hegde
8 Replies
funcone(1)							SAORD Documentation							funcone(1)

NAME
funcone - cone search of a binary table containing RA, Dec columns SYNOPSIS
funcone <switches> <iname> <oname> <ra[hdr]> <dec[hdr]> <radius[dr'"]> [columns] OPTIONS
-d deccol:[hdr] # Dec column name, units (def: DEC:d) -j # join columns from list file -J # join columns from list file, output all rows -l listfile # read centers and radii from a list -L listfile # read centers and radii from a list, output list rows -n # don't use cone limits as a filter -r racol:[hdr] # RA column name, units (def: RA:h) -x # append RA_CEN, DEC_CEN, RAD_CEN, CONE_KEY cols -X # append RA_CEN, DEC_CEN, RAD_CEN, CONE_KEY cols, output all rows DESCRIPTION
Funcone performs a cone search on the RA and Dec columns of a FITS binary table. The distance from the center RA, Dec position to the RA, Dec in each row in the table is calculated. Rows whose distance is less than the specified radius are output. The first argument to the program specifies the FITS file, raw event file, or raw array file. If "stdin" is specified, data are read from the standard input. Use Funtools Bracket Notation to specify FITS extensions, and filters. The second argument is the output FITS file. If "stdout" is specified, the FITS binary table is written to the standard output. The third and fourth required arguments are the RA and Dec center position. By default, RA is specified in hours while Dec is specified in degrees. You can change the units of either of these by appending the character "d" (degrees), "h" (hours) or "r" (radians). Sexagesimal notation is supported, with colons or spaces separating hms and dms. (When using spaces, please ensure that the entire string is quoted.) The fifth required argument is the radius of the cone search. By default, the radius value is given in degrees. The units can be changed by appending the character "d" (degrees), "r" (radians), "'" (arc minutes) or '"' (arc seconds). By default, all columns of the input file are copied to the output file. Selected columns can be output using an optional sixth argument in the form: "column1 column1 ... columnN" A seventh argument allows you to output selected columns from the list file when -j switch is used. Note that the RA and Dec columns used in the cone calculation must not be de-selected. Also by default, the RA and Dec column names are named "RA" and "Dec", and are given in units of hours and degrees respectively. You can change both the name and the units using the -r [RA] and/or -d [Dec] switches. Once again, one of "h", "d", or "r" is appended to the column name to specify units but in this case, there must be a colon ":" between the name and the unit specification. If the -l [listfile] switch is used, then one or more of the center RA, center Dec, and radius can be taken from a list file (which can be a FITS table or an ASCII column text file). In this case, the third (center RA), fourth (center Dec), and fifth (radius) command line argu- ments can either be a column name in the list file (if that parameter varies) or else a numeric value (if that parameter is static). When a column name is specified for the RA, Dec, or radius, you can append a colon followed by "h", "d", or "r" to specify units (also ' and " for radius). The cone search algorithm is run once for each row in the list, taking RA, Dec, and radius values from the specified columns or from static numeric values specified on the command line. When using a list, all valid rows from each iteration are written to a single output file. Use the -x switch to help delineate which line of the list file was used to produce the given output row(s). This switch causes the values for the center RA, Dec, radius, and row number to be appended to the output file, in columns called RA_CEN, DEC_CEN, RAD_CEN and CONE_KEY, respectively. Alternatively, the -j (join) switch will append all columns from the list row to the output row (essentially a join of the list row and input row), along with the CONE_KEY row number. These two switches are mutually exclusive. The -X and -J switches write out the same data as their lower case counterparts for each row satisfying a cone search. In addition, these switches also write out rows from the event file that do not satisfy any cone search. In such cases, that CONE_KEY column will be given a value of -1 and the center and list position information will be set to zero for the given row. Thus, all rows of the input event file are guaranteed to be output, with rows satisfying at least one cone search having additional search information. The -L switch acts similarly to the -l switch in that it takes centers from a list file. However, it also implicitly sets the -j switch, so that output rows are the join of the input event row and the center position row. In addition, this switch also writes out all center position rows for which no event satisfies the cone search criteria of that row. The CONE_KEY column will be given a value of -2 for cen- ter rows that were not close to any data row and the event columns will be zeroed out for such rows. In this way, all centers rows are guaranteed to be output at least once. If any of "all row" switches (-X, -J, or -L) are specified, then a new column named JSTAT is added to the output table. The positive values in this column indicate the center position row number (starting from 1) in the list file that this data row successful matched in a cone search. A value of -1 means that the data row did not match any center position. A value of -2 means that the center position was not matched by any data row. Given a center position and radius, the cone search algorithm calculates limit parameters for a box enclosing the specified cone, and only tests rows whose positions values lie within those limits. For small files, the overhead associated with this cone limit filtering can cause the program to run more slowly than if all events were tested. You can turn off cone limit filtering using the -n switch to see if this speeds up the processing (especially useful when processing a large list of positions). For example, the default cone search uses columns "RA" and "Dec" in hours and degrees (respectively) and RA position in hours, Dec and radius in degrees: funone in.fits out.fits 23.45 34.56 0.01 To specify the RA position in degrees: funcone in.fits out.fits 23.45d 34.56 0.01 To get RA and Dec from a list but use a static value for radius (and also write identifying info for each row in the list): funcone -x -l list.txt in.fits out.fits MYRA MYDec 0.01 User specified columns in degrees, RA position in hours (sexagesimal notation), Dec position in degrees (sexagesimal notation) and radius in arc minutes: funcone -r myRa:d -d myDec in.fits out.fits 12:30:15.5 30:12 15' SEE ALSO
See funtools(7) for a list of Funtools help pages version 1.4.2 January 2, 2008 funcone(1)
All times are GMT -4. The time now is 08:08 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy